/*
 * @(#)UserRecord.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;

import org.apache.commons.dbutils.DbUtils;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.Crypt;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.PermissionManager;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.staff.bean.StaffRecord;
import com.dcivision.user.UserErrorConstant;
import com.dcivision.user.bean.UserMember;
import com.dcivision.user.bean.UserRecord;
import com.dcivision.user.web.ListUserMemberForm;
import com.dcivision.workflow.bean.MtmWorkflowStepUserActor;

/**
  UserRecordDAObject.java

  This class is the data access bean for table "USER_RECORD".

  @author           Rollo Chan
  @company          DCIVision Limited
  @creation date    15/07/2003
  @version          $Revision: 1.117.2.4 $
*/

/* ********************************Modify History***********************************************
 * Modify by  : Jicle.Wang 
 * Modify date: 2006/07/04
 * BugID      : 5309
 * Description: Add two methods
 *              1,Search and Sort in the Group's List User Mumber.
 *              2,Search and Sort in the Role's List User Mumber.
 * *********************************************************************************************
 */
public class UserRecordDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.117.2.4 $";

  public static final String TABLE_NAME = "USER_RECORD";
  
  public static final String E_MAIL = "E_MAIL";
  public static final String FULL_NAME = "FULL_NAME";
  public static final String ID = "ID";
  public static final String RECORD_TYPE = "RECORD_TYPE";
  public static final String TYPE_GROUP = "G";
  public static final String TYPE_ROLE = "R";
  public static final String TYPE_HIERARCHY = "S";
  public static final String TITLE = "TITLE";
  public static final String LVL1_NAME = "LVL1_NAME";
  public static final String LVL2_NAME = "LVL2_NAME";
  public static final String LVL3_NAME = "LVL3_NAME";
  public static final String LVL4_NAME = "LVL4_NAME";
  public static final String LVL5_NAME = "LVL5_NAME";
  public static final String LVL6_NAME = "LVL6_NAME";
  public static final String LVL7_NAME = "LVL7_NAME";
  public static final String LVL8_NAME = "LVL8_NAME";
  public static final String LVL9_NAME = "LVL9_NAME";
  public static final String LVL10_NAME = "LVL10_NAME";

  public UserRecordDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("LOGIN_NAME");
    this.vecDBColumn.add("LOGIN_PWD");
    this.vecDBColumn.add("FIRST_NAME");
    this.vecDBColumn.add("LAST_NAME");
    this.vecDBColumn.add("FULL_NAME");
    this.vecDBColumn.add("EMAIL");
    this.vecDBColumn.add("FAILED_ATTEMPT");
    this.vecDBColumn.add("MAX_ATTEMPT");
    this.vecDBColumn.add("PWD_EXPIRY_DAY");
    this.vecDBColumn.add("LAST_PWD_UPDATE_DATE");
    this.vecDBColumn.add("STATUS");
    this.vecDBColumn.add("USER_TYPE");
    this.vecDBColumn.add("USER_LINK_ID");
    this.vecDBColumn.add("PREFERENCE");
    this.vecDBColumn.add("LOCALE");
    this.vecDBColumn.add("USER_DEF_1");
    this.vecDBColumn.add("USER_DEF_2");
    this.vecDBColumn.add("USER_DEF_3");
    this.vecDBColumn.add("USER_DEF_4");
    this.vecDBColumn.add("USER_DEF_5");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }
 
  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        } else {
          //throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public synchronized AbstractBaseObject getByLogin(String loginName) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.LOGIN_NAME = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, loginName);

        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        } else {
          //throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }



  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    com.dcivision.user.web.ListUserRecordForm listRecordForm = (com.dcivision.user.web.ListUserRecordForm)searchForm;

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        if (!Utility.isEmpty(listRecordForm.getUserGroupID())) {
          sqlStat.append(", MTM_USER_RECORD_USER_GROUP B ");
        }
        if (!Utility.isEmpty(listRecordForm.getUserRoleID())) {
          sqlStat.append(", MTM_USER_RECORD_USER_ROLE C ");
        }
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        if(!Utility.isEmpty(listRecordForm.getStatus())){
          sqlStat.append(" AND A.STATUS = ? ");
        }
        //simon_add_S
        if(Utility.isEmpty(listRecordForm.getGrantShowAdmin())){
          sqlStat.append(" AND A.ID > 0 ");
        }else{
          sqlStat.append(" AND A.ID >= 0 ");
        }
        //simon_add_E
        if (!Utility.isEmpty(listRecordForm.getUserGroupID())) {
          sqlStat.append(" AND (A.ID = B.USER_RECORD_ID AND B.USER_GROUP_ID = " + listRecordForm.getUserGroupID() + ") ");
        }
        if (!Utility.isEmpty(listRecordForm.getUserRoleID())) {
          sqlStat.append(" AND (A.ID = C.USER_RECORD_ID AND C.USER_ROLE_ID = " + listRecordForm.getUserRoleID() + ") ");
        }

        sqlStat.append(this.getIDFilterWhereClause("A"));

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        int i=1;
        if(!Utility.isEmpty(listRecordForm.getStatus())){
          this.setPrepareStatement(preStatCnt, ++i, listRecordForm.getStatus());
        }
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, ++i, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        int j=1;
        if(!Utility.isEmpty(listRecordForm.getStatus())){
          this.setPrepareStatement(preStat, ++j, listRecordForm.getStatus());
        }
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, ++j, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          tmpUserRecord.setRecordCount(totalNumOfRecord);
          tmpUserRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpUserRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("AND A.STATUS <> ? ");
        sqlStat.append("AND A.ID > 0 ");
        sqlStat.append(this.getIDFilterWhereClause("A"));
        sqlStat.append("ORDER BY A.FULL_NAME ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_INACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          result.add(tmpUserRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
    UserRecord curRecord = (UserRecord) obj;
    TextUtility.stringValidation(curRecord.getFirstName());
    TextUtility.stringValidation(curRecord.getLastName());
    UserRecord existsRecord = (UserRecord) this.getObjectByLoginName(encryptLoginName(curRecord.getLoginName()));
    if (existsRecord != null){
    	if (GlobalConstant.STATUS_INACTIVE.equals(existsRecord.getStatus()) && GlobalConstant.RECORD_STATUS_ACTIVE.equals(existsRecord.getRecordStatus())){
    		throw new ApplicationException(UserErrorConstant.INACTIVE_ACCOUNT, null, curRecord.getLoginName());
    	}
    	else if (GlobalConstant.RECORD_STATUS_ACTIVE.equals(existsRecord.getRecordStatus())) {
    		throw new ApplicationException(UserErrorConstant.DUPLICATE_LOGIN_NAME, null, curRecord.getLoginName());
    	}
    }

    if (!Utility.isEmpty(curRecord.getUserType())) {
      curRecord.setUserType(curRecord.getUserType().substring(0, 1));
    }
  }

  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
    UserRecord curRecord = (UserRecord) obj;
    //Check duplicate login name exists
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.LOGIN_NAME = ? AND A.RECORD_STATUS = ? AND A.ID <> ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, encryptLoginName(curRecord.getLoginName()));
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, curRecord.getID());
        rs = preStat.executeQuery();
        if (rs.next()) {
          throw new ApplicationException(UserErrorConstant.DUPLICATE_LOGIN_NAME, null, curRecord.getLoginName());
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   USER_RECORD(ID, LOGIN_NAME, LOGIN_PWD, FIRST_NAME, LAST_NAME, FULL_NAME, EMAIL, FAILED_ATTEMPT, MAX_ATTEMPT, PWD_EXPIRY_DAY, LAST_PWD_UPDATE_DATE, STATUS, USER_TYPE, USER_LINK_ID, PREFERENCE, LOCALE, USER_DEF_1, USER_DEF_2, USER_DEF_3, USER_DEF_4, USER_DEF_5, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, MODIFY_LOGIN_PWD ) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, encryptLoginName(tmpUserRecord.getLoginName()));
        setPrepareStatement(preStat, 3, Crypt.encrypt(tmpUserRecord.getLoginPwd(), SystemParameterFactory.getSystemParameter(SystemParameterConstant.CRYPTO_SALT)));
        setPrepareStatement(preStat, 4, tmpUserRecord.getFirstName());
        setPrepareStatement(preStat, 5, tmpUserRecord.getLastName());
        setPrepareStatement(preStat, 6, tmpUserRecord.getFullName());
        setPrepareStatement(preStat, 7, tmpUserRecord.getEmail());
        setPrepareStatement(preStat, 8, tmpUserRecord.getFailedAttempt());
        setPrepareStatement(preStat, 9, tmpUserRecord.getMaxAttempt());
        setPrepareStatement(preStat, 10, tmpUserRecord.getPwdExpiryDay());
        if (tmpUserRecord.getLastPwdUpdateDate()==null) {
          setPrepareStatement(preStat, 11, currTime);
        } else {
          setPrepareStatement(preStat, 11, tmpUserRecord.getLastPwdUpdateDate());
        }
        setPrepareStatement(preStat, 12, tmpUserRecord.getStatus());
        setPrepareStatement(preStat, 13, tmpUserRecord.getUserType());
        setPrepareStatement(preStat, 14, tmpUserRecord.getUserLinkID());
        setPrepareStatement(preStat, 15, tmpUserRecord.getPreference());
        setPrepareStatement(preStat, 16, tmpUserRecord.getLocale());
        setPrepareStatement(preStat, 17, tmpUserRecord.getUserDef1());
        setPrepareStatement(preStat, 18, tmpUserRecord.getUserDef2());
        setPrepareStatement(preStat, 19, tmpUserRecord.getUserDef3());
        setPrepareStatement(preStat, 20, tmpUserRecord.getUserDef4());
        setPrepareStatement(preStat, 21, tmpUserRecord.getUserDef5());
        setPrepareStatement(preStat, 22, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 23, new Integer(0));
        setPrepareStatement(preStat, 24, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 25, currTime);
        setPrepareStatement(preStat, 26, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 27, currTime);
        setPrepareStatement(preStat,28, tmpUserRecord.getModifyLoginPwd());
        preStat.executeUpdate();
        tmpUserRecord.setID(nextID);
        tmpUserRecord.setCreatorID(sessionContainer.getUserRecordID());
        tmpUserRecord.setCreateDate(currTime);
        tmpUserRecord.setUpdaterID(sessionContainer.getUserRecordID());
        tmpUserRecord.setUpdateDate(currTime);
        tmpUserRecord.setUpdateCount(new Integer(0));
        tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
        tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
        return(tmpUserRecord);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

    synchronized(dbConn) {
      try {
        String optionalStr = "";
        int loopCnt = 2;

        if (!Utility.isEmpty(tmpUserRecord.getLoginPwd())) {
          optionalStr = "LOGIN_PWD=?, ";
          loopCnt = 3;
        }

        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE USER_RECORD ");
        sqlStat.append("SET    LOGIN_NAME=?, " + optionalStr + "FIRST_NAME=?, LAST_NAME=?, FULL_NAME=?, EMAIL=?, FAILED_ATTEMPT=?, MAX_ATTEMPT=?, PWD_EXPIRY_DAY=?, STATUS=?, USER_TYPE=?, USER_LINK_ID=?, PREFERENCE=?, LOCALE=?, USER_DEF_1=?, USER_DEF_2=?, USER_DEF_3=?, USER_DEF_4=?, USER_DEF_5=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=?, MODIFY_LOGIN_PWD=? ");
        
        if (!Utility.isEmpty(tmpUserRecord.getLoginPwd())){
        	sqlStat.append(" , LAST_PWD_UPDATE_DATE = ? ");
        }
        
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, encryptLoginName(tmpUserRecord.getLoginName()) );
        if (!Utility.isEmpty(tmpUserRecord.getLoginPwd())) {
          setPrepareStatement(preStat, 2, Crypt.encrypt(tmpUserRecord.getLoginPwd(), SystemParameterFactory.getSystemParameter(SystemParameterConstant.CRYPTO_SALT)));
        }
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getFirstName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getLastName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getFullName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getEmail());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getFailedAttempt());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getMaxAttempt());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getPwdExpiryDay());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getStatus());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserType());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserLinkID());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getPreference());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getLocale());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserDef1());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserDef2());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserDef3());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserDef4());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserDef5());
        setPrepareStatement(preStat, loopCnt++, new Integer(tmpUserRecord.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, loopCnt++, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, loopCnt++, currTime);
        setPrepareStatement(preStat,loopCnt++,tmpUserRecord.getModifyLoginPwd());
        
        if (!Utility.isEmpty(tmpUserRecord.getLoginPwd())){
        	setPrepareStatement(preStat,loopCnt++,currTime);
        }
        
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getID());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpUserRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpUserRecord.setUpdateDate(currTime);
          tmpUserRecord.setUpdateCount(new Integer(tmpUserRecord.getUpdateCount().intValue() + 1));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

   public synchronized AbstractBaseObject updateByFile(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

    synchronized(dbConn) {
      try {
        String optionalStr = "";
        int loopCnt = 1;


        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE USER_RECORD ");
        sqlStat.append("SET     FIRST_NAME=?, LAST_NAME=?, FULL_NAME=? , EMAIL=?, STATUS=?, USER_LINK_ID=?, UPDATE_COUNT=UPDATE_COUNT+1, UPDATER_ID=?, UPDATE_DATE=? ");
        sqlStat.append("WHERE  LOGIN_NAME=?  ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getFirstName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getLastName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getFullName());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getEmail());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getStatus());
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getUserLinkID());
        setPrepareStatement(preStat, loopCnt++, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, loopCnt++, currTime);
        setPrepareStatement(preStat, loopCnt++, tmpUserRecord.getLoginName());

        updateCnt = preStat.executeUpdate();
        //preStat.getConnection().commit();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpUserRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpUserRecord.setUpdateDate(currTime);
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  public synchronized AbstractBaseObject insertByFile(AbstractBaseObject obj) throws ApplicationException {
   PreparedStatement preStat = null;
   StringBuffer sqlStat = new StringBuffer();
   UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

   synchronized(dbConn) {
     try {
       String optionalStr = "";
       int loopCnt = 1;

        Integer nextID = getNextPrimaryID();
       int updateCnt = 0;
       Timestamp currTime = Utility.getCurrentTimestamp();
       sqlStat.append("INSERT ");
       sqlStat.append("INTO   USER_RECORD(ID, LOGIN_NAME, LOGIN_PWD, FIRST_NAME, LAST_NAME, FULL_NAME, EMAIL, FAILED_ATTEMPT, MAX_ATTEMPT, PWD_EXPIRY_DAY, LAST_PWD_UPDATE_DATE, STATUS, USER_TYPE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE,USER_LINK_ID, MODIFY_LOGIN_PWD ) ");
       sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?) ");

       preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
       setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, encryptLoginName(tmpUserRecord.getLoginName()));
        setPrepareStatement(preStat, 3, Crypt.encrypt(tmpUserRecord.getLoginPwd(), SystemParameterFactory.getSystemParameter(SystemParameterConstant.CRYPTO_SALT)));
        setPrepareStatement(preStat, 4, tmpUserRecord.getFirstName());
        setPrepareStatement(preStat, 5, tmpUserRecord.getLastName());
        setPrepareStatement(preStat, 6, tmpUserRecord.getFullName());
        setPrepareStatement(preStat, 7, tmpUserRecord.getEmail());
        setPrepareStatement(preStat, 8, tmpUserRecord.getFailedAttempt());
        setPrepareStatement(preStat, 9, tmpUserRecord.getMaxAttempt());
        setPrepareStatement(preStat, 10, tmpUserRecord.getPwdExpiryDay());
        if (tmpUserRecord.getLastPwdUpdateDate()==null) {
          setPrepareStatement(preStat, 11, currTime);
        } else {
          setPrepareStatement(preStat, 11, tmpUserRecord.getLastPwdUpdateDate());
        }
        setPrepareStatement(preStat, 12, tmpUserRecord.getStatus());
        setPrepareStatement(preStat, 13, tmpUserRecord.getUserType());
        setPrepareStatement(preStat, 14, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 15, new Integer(0));
        setPrepareStatement(preStat, 16, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 17, currTime);
        setPrepareStatement(preStat, 18, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 19, currTime);
        setPrepareStatement(preStat, 20, tmpUserRecord.getUserLinkID());
        setPrepareStatement(preStat,21,tmpUserRecord.getModifyLoginPwd());

       updateCnt = preStat.executeUpdate();
       //preStat.getConnection().commit();
       if (updateCnt == 0) {
         throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
       } else {
    	 tmpUserRecord.setID(nextID);
         tmpUserRecord.setUpdaterID(sessionContainer.getUserRecordID());
         tmpUserRecord.setUpdateDate(currTime);
         tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
         tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
         return(tmpUserRecord);
       }
     } catch (ApplicationException appEx) {
       throw appEx;
     } catch (SQLException sqle) {
       log.error(sqle, sqle);
       throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
     } catch (Exception e) {
       log.error(e, e);
       throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
     } finally {
       try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
     }
   }
 }



  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   USER_RECORD ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpUserRecord.getID());
        setPrepareStatement(preStat, 2, tmpUserRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpUserRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    UserRecord tmpUserRecord = (UserRecord)this.oldValue;

    if (tmpUserRecord != null) {
      oldValues.add(toAuditTrailValue(tmpUserRecord.getID()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getLoginName()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getLoginPwd()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getFirstName()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getLastName()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getFullName()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getEmail()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getFailedAttempt()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getMaxAttempt()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getPwdExpiryDay()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getLastPwdUpdateDate()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getStatus()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserType()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserLinkID()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getPreference()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getLocale()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserDef1()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserDef2()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserDef3()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserDef4()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUserDef5()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpUserRecord.getUpdateDate()));
    }

    tmpUserRecord = (UserRecord)obj;
    if (tmpUserRecord != null) {
      newValues.add(toAuditTrailValue(tmpUserRecord.getID()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getLoginName()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getLoginPwd()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getFirstName()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getLastName()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getFullName()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getEmail()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getFailedAttempt()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getMaxAttempt()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getPwdExpiryDay()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getLastPwdUpdateDate()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getStatus()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserType()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserLinkID()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getPreference()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getLocale()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserDef1()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserDef2()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserDef3()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserDef4()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUserDef5()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpUserRecord.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/

  private String encryptLoginName(String loginName) {
    if (Boolean.getBoolean(SystemParameterFactory.getSystemParameter(SystemParameterConstant.LOGIN_NAME_ENCRYPTION_ENABLE))) {
      return(Crypt.encrypt(loginName, SystemParameterFactory.getSystemParameter(SystemParameterConstant.CRYPTO_SALT)));
    } else {
      return(loginName);
    }
  }

  private String decryptLoginName(String loginName) {
    if (Boolean.getBoolean(SystemParameterFactory.getSystemParameter(SystemParameterConstant.LOGIN_NAME_ENCRYPTION_ENABLE))) {
      return(Crypt.decrypt(loginName, SystemParameterFactory.getSystemParameter(SystemParameterConstant.CRYPTO_SALT)));
    } else {
      return(loginName);
    }
  }

  public synchronized AbstractBaseObject getObjectByUserTypeUserLinkID(String userType, Integer userLinkID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.USER_TYPE = ? AND A.USER_LINK_ID = ? AND A.STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, userType);
        this.setPrepareStatement(preStat, 2, userLinkID);
        this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        } else {
          return(null);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized AbstractBaseObject getObjectByLoginName(String loginName) throws ApplicationException {
    return(this.getObjectByLoginName(loginName, null));
  }

  public synchronized AbstractBaseObject getObjectByLoginName(String loginName, String recordStatus) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.LOGIN_NAME = ? ");
        if (Utility.isEmpty(recordStatus)) {
          sqlStat.append("ORDER BY A.UPDATE_DATE DESC ");
        } else {
          sqlStat.append("AND A.RECORD_STATUS='" + recordStatus + "' ");
        }
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, encryptLoginName(loginName));
        rs = preStat.executeQuery();
        if (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        } else {
          return(null);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized AbstractBaseObject updateFailedAttempt(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    UserRecord tmpUserRecord = (UserRecord)((UserRecord)obj).clone();

    synchronized(dbConn) {
      try {
          int updateCnt = 0;
          Timestamp currTime = Utility.getCurrentTimestamp();
          sqlStat.append("UPDATE USER_RECORD ");
          sqlStat.append("SET FAILED_ATTEMPT=? ");
          sqlStat.append("WHERE ID=? AND UPDATE_DATE=? ");
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          setPrepareStatement(preStat, 1, tmpUserRecord.getFailedAttempt());
          setPrepareStatement(preStat, 2, tmpUserRecord.getID());
          setPrepareStatement(preStat, 3, tmpUserRecord.getUpdateDate());
          updateCnt = preStat.executeUpdate();
          if (updateCnt == 0) {
              throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
          } else {
              return(tmpUserRecord);
          }
      } catch (ApplicationException appEx) {
          throw appEx;
      } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Return the array of string[] to show the permission of users.<br>
   * [0] User Record ID<br>
   * [1] Full Name<br>
   * [2] Subject Type(Should be "U")<br>
   * [3] Permission String<br>
   * [4] Start Time<br>
   * [5] End Time<br>
   * [6] Permission must flag<br>
   * [7] Permimssion ID<br>
   *
   * @param objType
   * @param objID
   * @return
   * @throws ApplicationException
   */
  public synchronized String[][] getHasPermissionArrayByObjectTypeObjectID(String objType, Integer objID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FULL_NAME, B.PERMISSION, B.MUST_FLAG, B.START_TIME, B.END_TIME, B.ID AS PERM_ID ");
        sqlStat.append("FROM   USER_RECORD A, USER_RECORD_PERMISSION B ");
        sqlStat.append("WHERE  A.ID > 0 AND A.ID = B.USER_RECORD_ID AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND B.OBJECT_TYPE = ? AND B.OBJECT_ID = ? ");
        sqlStat.append("AND A.STATUS = ? ");
        sqlStat.append("ORDER BY A.FULL_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, objType);
        this.setPrepareStatement(preStat, 4, objID);
        this.setPrepareStatement(preStat, 5, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          String startTime = TextUtility.formatDate(getResultSetTimestamp(rs, "START_TIME"), SystemParameterFactory.getSystemParameter(SystemParameterConstant.DB_DATETIME_FORMAT));
          String endTime = TextUtility.formatDate(getResultSetTimestamp(rs, "END_TIME"), SystemParameterFactory.getSystemParameter(SystemParameterConstant.DB_DATETIME_FORMAT));
          result.add(new String[] {getResultSetString(rs, "ID"), getResultSetString(rs, "FULL_NAME"), GlobalConstant.SUBJECT_TYPE_USER, getResultSetString(rs, "PERMISSION"), startTime, endTime, (GlobalConstant.TRUE.equals(getResultSetString(rs, "MUST_FLAG")) ? GlobalConstant.TRUE : GlobalConstant.FALSE), getResultSetString(rs, "PERM_ID")});
        }
        String[][] tmpAry = new String[result.size()][];
        for (int i = 0; i < result.size(); i++) {
          tmpAry[i] = (String[])result.get(i);
        }
        return(tmpAry);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Return the array of string[] to show the users who have no permission.<br>
   * [0] User Record ID<br>
   * [1] Full Name<br>
   * [2] Subject Type(Should be "U")<br>
   * [3] Empty Permission String<br>
   * [4] Empty Start Time<br>
   * [5] Empty End Time<br>
   * [6] "N" for initial must flag<br>
   * [7] "-1" for new permimssion ID<br>
   *
   * @param objType
   * @param objID
   * @return
   * @throws ApplicationException
   */
  public synchronized String[][] getHasNoPermissionArrayByObjectTypeObjectID(String objType, Integer objID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    String[][] hasAry = this.getHasPermissionArrayByObjectTypeObjectID(objType, objID);

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FULL_NAME ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.ID > 0 AND A.RECORD_STATUS = ? AND A.STATUS = ? ");
        if (hasAry.length > 0) {
          sqlStat.append("AND (");
          for (int i = 0; i < hasAry.length; i++) {
            sqlStat.append(" A.ID <> ");
            sqlStat.append(hasAry[i][0]);
            sqlStat.append(" AND");
          }
          sqlStat.delete(sqlStat.length()-3, sqlStat.length());
          sqlStat.append(") ");
        }
        sqlStat.append("ORDER BY A.FULL_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          result.add(new String[] {getResultSetString(rs, "ID"), getResultSetString(rs, "FULL_NAME"), GlobalConstant.SUBJECT_TYPE_USER, "", "", "", GlobalConstant.FALSE, "-1"});
        }
        String[][] tmpAry = new String[result.size()][];
        for (int i = 0; i < result.size(); i++) {
          tmpAry[i] = (String[])result.get(i);
        }
        return(tmpAry);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Return the array of string[] to store the user name and email.<br>
   * [0] Full Name<br>
   * [1] Email Address<br>
   *
   * @param id    The user record ID.
   * @return      The user name and email in String[].
   * @throws ApplicationException
   */
  public synchronized String[] getInactiveObjectByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.FULL_NAME, A.EMAIL ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.ID = ?");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        rs = preStat.executeQuery();
        if (rs.next()) {
          return (new String[] {rs.getString(1), rs.getString(2)});
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * getIDListByLists
   *
   * Pass a List of user ID, group ID, role ID and return all the user IDs found
   * in those groups and roles.
   *
   * @param userIDList        List of User Record ID.
   * @param groupIDList       List of User Group ID.
   * @param roleIDList        List of User Role ID.
   * @return                  A list of User Record IDs.
   * @throws ApplicationException
   */
  public synchronized List getIDListByLists(List userIDList, List groupIDList, List roleIDList) throws ApplicationException {
    if (Utility.isEmpty(userIDList) && Utility.isEmpty(groupIDList) && Utility.isEmpty(roleIDList)) {
      return(new ArrayList());
    }
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    Map hashTmp = new HashMap();
    List result = new ArrayList();
    List roleList = new ArrayList();

    if (!Utility.isEmpty(userIDList)) {
      for (int i = 0; i < userIDList.size(); i++) {
        Integer usdID = TextUtility.parseIntegerObj((String)userIDList.get(i));
        hashTmp.put(usdID, usdID);
      }
    }

    synchronized(dbConn) {
      if (!Utility.isEmpty(roleIDList)) {
        try {
          sqlStat.append("SELECT DISTINCT A.USER_RECORD_ID ");
          sqlStat.append("FROM   MTM_USER_RECORD_USER_ROLE A, USER_RECORD B ");
          sqlStat.append("WHERE  A.USER_RECORD_ID = B.ID AND B.STATUS = ? AND B.RECORD_STATUS = ? AND ");
          sqlStat.append("A.RECORD_STATUS = ? AND (A.USER_ROLE_ID = ");

          sqlStat.append(roleIDList.get(0) + " ");
          for (int i = 1; i < roleIDList.size(); i++) {
            sqlStat.append(" OR A.USER_ROLE_ID = " + roleIDList.get(i));
          }
          sqlStat.append(")");

          log.info("SQL: " + sqlStat);
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          this.setPrepareStatement(preStat, 1, GlobalConstant.STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
          rs = preStat.executeQuery();
          while (rs.next()) {
            hashTmp.put(this.getResultSetInteger(rs, 1), this.getResultSetInteger(rs, 1));
          }
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }

          sqlStat = new StringBuffer();
          sqlStat.append("SELECT DISTINCT A.USER_RECORD_ID ");
          sqlStat.append("FROM  MTM_USER_RECORD_USER_GROUP A, MTM_USER_GROUP_USER_ROLE B, USER_RECORD C ");
          sqlStat.append("WHERE A.USER_GROUP_ID = B.USER_GROUP_ID AND A.RECORD_STATUS = ? AND ");
          sqlStat.append("      A.USER_RECORD_ID = C.ID AND C.STATUS = ? AND C.RECORD_STATUS = ? AND ");
          sqlStat.append("      B.RECORD_STATUS = ? AND (B.USER_ROLE_ID = ");

          sqlStat.append(roleIDList.get(0) + " ");
          for (int i = 1; i < roleIDList.size(); i++) {
            sqlStat.append(" OR B.USER_ROLE_ID = " + roleIDList.get(i));
          }
          sqlStat.append(")");

          log.info("SQL: " + sqlStat);
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
          rs = preStat.executeQuery();
          while (rs.next()) {
            hashTmp.put(this.getResultSetInteger(rs, 1), this.getResultSetInteger(rs, 1));
          }
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
        } finally {
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }

      if (!Utility.isEmpty(groupIDList)) {
        try {
          sqlStat = new StringBuffer();
          sqlStat.append("SELECT DISTINCT A.USER_RECORD_ID ");
          sqlStat.append("FROM   MTM_USER_RECORD_USER_GROUP A, USER_RECORD B ");
          sqlStat.append("WHERE  A.USER_RECORD_ID = B.ID AND B.STATUS = ? AND B.RECORD_STATUS = ? AND ");
          sqlStat.append("A.RECORD_STATUS = ? AND (A.USER_GROUP_ID = ");

          sqlStat.append(groupIDList.get(0) + " ");
          for (int i = 1; i < groupIDList.size(); i++) {
            sqlStat.append(" OR A.USER_GROUP_ID = " + groupIDList.get(i));
          }
          sqlStat.append(")");

          log.info("SQL: " + sqlStat);
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          this.setPrepareStatement(preStat, 1, GlobalConstant.STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
          rs = preStat.executeQuery();
          while (rs.next()) {
            hashTmp.put(this.getResultSetInteger(rs, 1), this.getResultSetInteger(rs, 1));
          }
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
        } finally {
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
      hashTmp = new TreeMap(hashTmp);
      return(new ArrayList(hashTmp.values()));
    }
  }

  public synchronized List getUserRecordByUserGroupID(Integer groupID, Integer userID, String status) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID,  A.FIRST_NAME, A.LAST_NAME, A.USER_LINK_ID ");
        sqlStat.append("FROM   MTM_USER_RECORD_USER_GROUP B, USER_RECORD A  ");
        sqlStat.append("WHERE  (A.RECORD_STATUS=?) AND (A.STATUS=?) AND (B.USER_RECORD_ID = A.ID) AND (B.USER_GROUP_ID = ? ) ");
        sqlStat.append("ORDER BY A.FIRST_NAME ASC, A.LAST_NAME ASC, A.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, status);
        this.setPrepareStatement(preStat, 2, status);
        this.setPrepareStatement(preStat, 3, groupID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME") + " " + getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          if (!(getResultSetInteger(rs, "ID").equals(userID))) {
            result.add(tmpUserRecord);
          }
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized List getAllOtherUserByStatus(String status, Integer userID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.FIRST_NAME, A.LAST_NAME ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.STATUS = ? AND A.RECORD_STATUS = ? AND A.ID <> ? AND A.ID <> 0 ");
        sqlStat.append(this.getIDFilterWhereClause("A"));
        sqlStat.append("ORDER BY A.FIRST_NAME ASC, A.LAST_NAME ASC, A.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, status);
        this.setPrepareStatement(preStat, 2, status);
        this.setPrepareStatement(preStat, 3, userID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME") + " " + getResultSetString(rs, "LAST_NAME"));
          result.add(tmpUserRecord);
        }
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized List getListByStaffLevelNo(Integer levelNo, Integer staffHierarchyID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A, STAFF_RECORD B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.USER_TYPE = ? AND A.USER_LINK_ID = B.ID AND B.LEVEL_" + levelNo + "_ID = ? AND A.STATUS = ? ");
        sqlStat.append("ORDER BY FIRST_NAME ASC, LAST_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, UserRecord.USER_TYPE_STAFF);
        this.setPrepareStatement(preStat, 3, staffHierarchyID);
        this.setPrepareStatement(preStat, 4, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          result.add(tmpUserRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }

  }
  //get underling uesr list by current staff levelNo and staffHierarchyID;
  public synchronized List getUnderlingListByStaffLevelNo(Integer levelNo, Integer staffHierarchyID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    Integer underLevelNo=new Integer(levelNo.intValue()+1);
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A, STAFF_RECORD B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.USER_TYPE = ? AND A.USER_LINK_ID = B.ID AND B.LEVEL_" + levelNo + "_ID = ? AND B.LEVEL_" + underLevelNo + "_ID is not null AND A.STATUS = ? ");
        sqlStat.append("ORDER BY A.FIRST_NAME ASC, A.LAST_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, UserRecord.USER_TYPE_STAFF);
        this.setPrepareStatement(preStat, 3, staffHierarchyID);
        this.setPrepareStatement(preStat, 4, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          result.add(tmpUserRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }

  }

  public synchronized AbstractBaseObject getReportToUserByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A, STAFF_RECORD B, USER_RECORD C ");
        sqlStat.append("WHERE  A.USER_LINK_ID=B.REPORT_TO_STAFF_RECORD_ID AND B.ID=C.USER_LINK_ID ");
        sqlStat.append("       AND A.RECORD_STATUS = ? AND C.RECORD_STATUS = ? AND C.ID = ? AND A.STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, id);
        this.setPrepareStatement(preStat, 4, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          return(tmpUserRecord);
        } else {
          return(null);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized List getAllOtherUserFullNameByStatus(String status, Integer userID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.FIRST_NAME, A.LAST_NAME,A.FULL_NAME ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.STATUS = ? AND A.RECORD_STATUS = ? AND A.ID <> ? AND A.ID <> 0 ");
        sqlStat.append(this.getIDFilterWhereClause("A"));
        sqlStat.append("ORDER BY A.FIRST_NAME ASC, A.LAST_NAME ASC, A.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, status);
        this.setPrepareStatement(preStat, 2, status);
        this.setPrepareStatement(preStat, 3, userID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
//          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME") + " " + getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FULL_NAME"));
          result.add(tmpUserRecord);
        }
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  public synchronized List getUserRecordFullNameByUserGroupID(Integer groupID, Integer userID, String status) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID,  A.FIRST_NAME, A.LAST_NAME,A.Full_Name ");
        sqlStat.append("FROM   MTM_USER_RECORD_USER_GROUP B, USER_RECORD A  ");
        sqlStat.append("WHERE  (A.RECORD_STATUS=?) AND (A.STATUS=?) AND (B.USER_RECORD_ID = A.ID) AND (B.USER_GROUP_ID = ? ) ");
        sqlStat.append("ORDER BY A.FIRST_NAME ASC, A.LAST_NAME ASC, A.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, status);
        this.setPrepareStatement(preStat, 2, status);
        this.setPrepareStatement(preStat, 3, groupID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
//          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME") + " " + getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FULL_NAME"));
          if (!(getResultSetInteger(rs, "ID").equals(userID))) {
            result.add(tmpUserRecord);
          }
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   *   Validate selected staff record of current user record,<br>
   * avoid the selected staff belong to an existing user.   <br>
   * 
   * @param currentUserLinkID  Current checked staff record ID.
   * @param currentUserID      Current user ID.
   * @return boolean           
   *         True              No other user has the current selected staff ID.
   *         False             The current selected staff ID has been other user. 
   * @throws ApplicationException
   */
  public synchronized boolean isStaffIDHasChecked(String currentStaffIDOfUser,Integer currentUserID) throws ApplicationException{
    PreparedStatement preStat = null;
	ResultSet rs = null;
	StringBuffer sqlStat = new StringBuffer();
    boolean flag=true;

	synchronized (dbConn) {
	  try {
	    sqlStat.append(" SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
	    sqlStat.append(" FROM   USER_RECORD A ");
	    sqlStat.append(" WHERE  A.ID<>? AND A.USER_LINK_ID=? ");
	    sqlStat.append(" AND    A.RECORD_STATUS=? ");
	    preStat = dbConn.prepareStatement(sqlStat.toString());
	    this.setPrepareStatement(preStat, 1, currentUserID);
	    this.setPrepareStatement(preStat, 2, currentStaffIDOfUser);
	    this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
	    rs = preStat.executeQuery();
	    if(rs.next()){
	      flag=false;
	    }
	    return flag;
	  } catch (SQLException sqle) {
	    log.error(sqle, sqle);
	    throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	  } catch (Exception e) {
	    log.error(e, e);
	    throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	  } finally {
	    try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	    try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	  }
	}
  }
  public synchronized List getAllUserList() throws ApplicationException {
      PreparedStatement preStat = null;
      ResultSet rs = null;
      StringBuffer sqlStat = new StringBuffer();
      List result = new ArrayList();

      synchronized(dbConn) {
        try {
          sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
          sqlStat.append("FROM   USER_RECORD A ");
          //sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
          sqlStat.append(this.getIDFilterWhereClause("A"));
          sqlStat.append("ORDER BY A.FULL_NAME ");
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          //this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
          rs = preStat.executeQuery();
          while (rs.next()) {
            UserRecord tmpUserRecord = new UserRecord();
            tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
            tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
            tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
            tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
            tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
            tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
            tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
            tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
            tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
            tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
            tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
            tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
            tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
            tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
            tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
            tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
            tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
            tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
            tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
            tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
            tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
            tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
            tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
            tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
            tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
            tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
            tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
            tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
            tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
            tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
            result.add(tmpUserRecord);
          }
          return(result);
        } catch (ApplicationException appEx) {
          throw appEx;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
        } finally {
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
    }

  /**
   * get user record list by staff record list
   * @param staffRecordList
   * @return
   * @throws ApplicationException
   */
  public synchronized List getListByStaffRecordList(List staffRecordList) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    String staffRecordIDStr = "";
    if(Utility.isEmpty(staffRecordList)){
      return result;
    } else {
      for(int i=0; i<staffRecordList.size(); i++){
        if (i!=0) {
          staffRecordIDStr += ",";
        }
        StaffRecord staffRecord = (StaffRecord)staffRecordList.get(i);
        staffRecordIDStr += staffRecord.getID().toString();
      }
    }

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.USER_TYPE = ? AND A.USER_LINK_ID IN ("+staffRecordIDStr+")  AND A.STATUS = ? ");
        sqlStat.append("ORDER BY FIRST_NAME ASC, LAST_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, UserRecord.USER_TYPE_STAFF);
        //this.setPrepareStatement(preStat, 3, staffRecordIDStr);
        this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          UserRecord tmpUserRecord = new UserRecord();
          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
          result.add(tmpUserRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }

  }

  /**
   * filter user record ID list by the staff level index.
   * @param userRecordIDList
   * @param levelIndex
   * @return
   * @throws ApplicationException
   */
  public synchronized List filterUserRecordIDByListLevelIndex(
      List userRecordIDList,
      String levelIndex)
      throws ApplicationException {
     PreparedStatement preStat = null;
     ResultSet rs = null;
     StringBuffer sqlStat = new StringBuffer();
     List result = new ArrayList();
     String userRecordIDs = "";
     String nextLevelIndex = new Integer((new Integer(levelIndex).intValue()+1)).toString();
     synchronized(dbConn) {
       try {
         sqlStat.append(" SELECT A.ID ");
         sqlStat.append(" FROM   USER_RECORD A, STAFF_RECORD B ");
         sqlStat.append(" WHERE   B.LEVEL_"+levelIndex+"_ID IS NOT NULL AND B.LEVEL_"+nextLevelIndex+"_ID IS NULL AND B.RECORD_STATUS = ? ");
         sqlStat.append(" AND     B.ID = A.USER_LINK_ID ");
         sqlStat.append(" AND     A.RECORD_STATUS = B.RECORD_STATUS ");
         for(int i = 0; i < userRecordIDList.size(); i++){
           if(i == 0){
             userRecordIDs = userRecordIDList.get(i).toString();
           } else {
             userRecordIDs += "," + userRecordIDList.get(i).toString();
           }
         }
         if(!Utility.isEmpty(userRecordIDs)){
            sqlStat.append(" AND     A.ID IN (" + userRecordIDs + ") ");
         }

         preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
         this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
         rs = preStat.executeQuery();
         while (rs.next()) {
           result.add(getResultSetInteger(rs, "ID"));
         }
         return(result);
       } catch (ApplicationException appEx) {
         throw appEx;
       } catch (SQLException sqle) {
         log.error(sqle, sqle);
         throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
       } catch (Exception e) {
         log.error(e, e);
         throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
       } finally {
         try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
         try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
       }
     }
   }
  
  public synchronized Timestamp getRelatedMostUpdateDate(String tableName) throws ApplicationException {
      PreparedStatement preStat = null;
      ResultSet rs = null;
      StringBuffer sqlStat = new StringBuffer();
      Timestamp mostUpdateDate = null;
      synchronized(dbConn) {
        try {
          sqlStat.append("SELECT MAX(UPDATE_DATE) AS MOST_UPDATE_DATE ");
          sqlStat.append("FROM  "+ tableName+ " " );
          sqlStat.append("WHERE  RECORD_STATUS = ? ");
         
          
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          
          this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
         
          rs = preStat.executeQuery();
          if (rs.next()) {          
            mostUpdateDate=getResultSetTimestamp(rs, "MOST_UPDATE_DATE");
          }
          return mostUpdateDate;
        } catch (ApplicationException appEx) {
          throw appEx;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
        } finally {
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
    }

  /**
   * Return the array of string[] to show the permission of users,this array has updater name .<br>
   * [0] User Record ID<br>
   * [1] Full Name<br>
   * [2] Subject Type(Should be "U")<br>
   * [3] Permission String<br>
   * [4] Start Time<br>
   * [5] End Time<br>
   * [6] Permission must flag<br>
   * [7] Permimssion ID<br>
   * [8] Updater full name
   *
   * @param objType
   * @param objID
   * @return
   * @throws ApplicationException
   */                            
  public synchronized String[][] getHasPermissionAndUpdaterArrayByObjectTypeObjectID(String objType, Integer objID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FULL_NAME, B.PERMISSION, B.MUST_FLAG, B.START_TIME, B.END_TIME, B.ID AS PERM_ID ,C.FULL_NAME AS UPDATER_FULL_NAME ");
        sqlStat.append("FROM   USER_RECORD A, USER_RECORD_PERMISSION B , USER_RECORD C ");
        sqlStat.append("WHERE  A.ID > 0 AND A.ID = B.USER_RECORD_ID AND C.ID = B.UPDATER_ID AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND B.OBJECT_TYPE = ? AND B.OBJECT_ID = ? ");
        sqlStat.append("AND A.STATUS = ? ");
        sqlStat.append("ORDER BY A.FULL_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, objType);
        this.setPrepareStatement(preStat, 4, objID);
        this.setPrepareStatement(preStat, 5, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          String startTime = TextUtility.formatDate(getResultSetTimestamp(rs, "START_TIME"), SystemParameterFactory.getSystemParameter(SystemParameterConstant.DB_DATETIME_FORMAT));
          String endTime = TextUtility.formatDate(getResultSetTimestamp(rs, "END_TIME"), SystemParameterFactory.getSystemParameter(SystemParameterConstant.DB_DATETIME_FORMAT));
          result.add(new String[] {getResultSetString(rs, "ID"), getResultSetString(rs, "FULL_NAME"), GlobalConstant.SUBJECT_TYPE_USER, getResultSetString(rs, "PERMISSION"), 
                startTime, endTime, (GlobalConstant.TRUE.equals(getResultSetString(rs, "MUST_FLAG")) ? GlobalConstant.TRUE : GlobalConstant.FALSE), getResultSetString(rs, "PERM_ID"),getResultSetString(rs, "UPDATER_FULL_NAME")});
        }
        String[][] tmpAry = new String[result.size()][];
        for (int i = 0; i < result.size(); i++) {
          tmpAry[i] = (String[])result.get(i);
        }
        return(tmpAry);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  /**
   * Return the array of string[] to show the users who have no permission. ,this array has updater name.<br>
   * [0] User Record ID<br>
   * [1] Full Name<br>
   * [2] Subject Type(Should be "U")<br>
   * [3] Empty Permission String<br>
   * [4] Empty Start Time<br>
   * [5] Empty End Time<br>
   * [6] "N" for initial must flag<br>
   * [7] "-1" for new permimssion ID<br>
   * [8] Updater full name
   *
   * @param objType
   * @param objID
   * @return
   * @throws ApplicationException
   */                            
  public synchronized String[][] getHasNoPermissionAndUpdaterArrayByObjectTypeObjectID(String objType, Integer objID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    String[][] hasAry = this.getHasPermissionAndUpdaterArrayByObjectTypeObjectID(objType, objID);

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FULL_NAME ");
        sqlStat.append("FROM   USER_RECORD A ");
        sqlStat.append("WHERE  A.ID > 0 AND A.RECORD_STATUS = ? AND A.STATUS = ? ");
        if (hasAry.length > 0) {
          sqlStat.append("AND (");
          for (int i = 0; i < hasAry.length; i++) {
            sqlStat.append(" A.ID <> ");
            sqlStat.append(hasAry[i][0]);
            sqlStat.append(" AND");
          }
          sqlStat.delete(sqlStat.length()-3, sqlStat.length());
          sqlStat.append(") ");
        }
        sqlStat.append("ORDER BY A.FULL_NAME ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          result.add(new String[] {getResultSetString(rs, "ID"), getResultSetString(rs, "FULL_NAME"), GlobalConstant.SUBJECT_TYPE_USER, "", "", "", GlobalConstant.FALSE, "-1"});
        }
        String[][] tmpAry = new String[result.size()][];
        for (int i = 0; i < result.size(); i++) {
          tmpAry[i] = (String[])result.get(i);
        }
        return(tmpAry);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }  
  
  /** get user object by email address
   * @param emailAddress
   * @return
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getUserByEmailAddress(String emailAddress) throws ApplicationException {
	    PreparedStatement preStat = null;
	    ResultSet rs = null;
	    StringBuffer sqlStat = new StringBuffer();

	    synchronized(dbConn) {
	      try {
	        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
	        sqlStat.append("FROM   USER_RECORD A ");
	        sqlStat.append("WHERE  A.EMAIL = ? AND A.RECORD_STATUS = ? ");
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        this.setPrepareStatement(preStat, 1, emailAddress);
	        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
	        rs = preStat.executeQuery();
	        if (rs.next()) {
	          UserRecord tmpUserRecord = new UserRecord();
	          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
	          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
	          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
	          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
	          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
	          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
	          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
	          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
	          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
	          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
	          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
	          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
	          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
	          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
	          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
	          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
	          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
	          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
	          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
	          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
	          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
	          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
	          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
	          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
	          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
	          return(tmpUserRecord);
	        } else {
	          return null;
	        }
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	      }
	    }
	}  
  
  public synchronized AbstractBaseObject getUserByID(Integer owerID) throws ApplicationException {
	  //UserRecord user = null;
	    PreparedStatement preStat = null;
	    ResultSet rs = null; 
	    StringBuffer sqlStat = new StringBuffer();

	    synchronized(dbConn) {
	      try {
	        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD, A.MODIFY_LOGIN_PWD ");
	        sqlStat.append("FROM   USER_RECORD A ");
	        sqlStat.append("WHERE  A.ID = ?");
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        this.setPrepareStatement(preStat, 1, owerID);
	        rs = preStat.executeQuery();
	        if (rs.next()) {
	          UserRecord tmpUserRecord = new UserRecord();
	          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
	          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
	          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
	          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
	          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
	          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
	          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
	          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
	          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
	          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
	          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
	          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
	          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
	          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
	          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
	          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
	          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
	          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
	          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
	          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
	          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
	          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
	          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
	          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
	          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
	          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
	          return(tmpUserRecord);
	        } else {
	          return null;
	        }
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	      }
	    }
  }
  
  public synchronized List getListAll(AbstractSearchForm searchForm) throws ApplicationException {
	    PreparedStatement preStat = null;
	    PreparedStatement preStatCnt = null;
	    ResultSet rs = null;
	    ResultSet rsCnt = null;
	    StringBuffer sqlStat = new StringBuffer();
	    StringBuffer sqlStatCnt = new StringBuffer();
	    List result = new ArrayList();
	    int totalNumOfRecord = 0;
	    int rowLoopCnt = 0;
	    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
	    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
	    com.dcivision.user.web.ListUserRecordForm listRecordForm = (com.dcivision.user.web.ListUserRecordForm)searchForm;

	    synchronized(dbConn) {
	      try {
	        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
	        sqlStat.append("FROM   USER_RECORD A ");
	        if (!Utility.isEmpty(listRecordForm.getUserGroupID())) {
	          sqlStat.append(", MTM_USER_RECORD_USER_GROUP B ");
	        }
	        if (!Utility.isEmpty(listRecordForm.getUserRoleID())) {
	          sqlStat.append(", MTM_USER_RECORD_USER_ROLE C ");
	        }
	        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
	        if(!Utility.isEmpty(listRecordForm.getStatus())){
	          sqlStat.append(" AND A.STATUS = ? ");
	        }
	        sqlStat.append(" AND A.ID > 0 ");

	        if (!Utility.isEmpty(listRecordForm.getUserGroupID())) {
	          sqlStat.append(" AND (A.ID = B.USER_RECORD_ID AND B.USER_GROUP_ID = " + listRecordForm.getUserGroupID() + ") ");
	        }
	        if (!Utility.isEmpty(listRecordForm.getUserRoleID())) {
	          sqlStat.append(" AND (A.ID = C.USER_RECORD_ID AND C.USER_ROLE_ID = " + listRecordForm.getUserRoleID() + ") ");
	        }

	        sqlStat.append(this.getIDFilterWhereClause("A"));

	        // Form the WHERE clause for filtering.
	        if (searchForm.isSearchable()) {
	          String searchField = getSearchColumn(searchForm.getBasicSearchField());
	          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
	        }

	        //format the sql for any 'LIKE' statement contained
	        sqlStat = this.getFormattedSQL(sqlStat.toString());

	        // Form the ORDER clause for sorting.
	        if (searchForm.isSortable()) {
	          String sortAttribute = searchForm.getSortAttribute();
	          if (sortAttribute.indexOf(".") < 0) {
	            sortAttribute = "A." + sortAttribute;
	          }
	          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
	        }

	        // Get total number of record return.
	        sqlStatCnt = this.getSelectCountSQL(sqlStat);
	        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
	        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
	        int i=1;
	        if(!Utility.isEmpty(listRecordForm.getStatus())){
	          this.setPrepareStatement(preStatCnt, ++i, listRecordForm.getStatus());
	        }
	        if (searchForm.isSearchable()) {
	          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
	          this.setPrepareStatement(preStatCnt, ++i, searchKeyword);
	        }
	        rsCnt = preStatCnt.executeQuery();
	        if (rsCnt.next()) {
	          totalNumOfRecord = rsCnt.getInt(1);
	        }
	        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
	        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

	        // Retrieve the result in row basis.
//	        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
	        int j=1;
	        if(!Utility.isEmpty(listRecordForm.getStatus())){
	          this.setPrepareStatement(preStat, ++j, listRecordForm.getStatus());
	        }
	        if (searchForm.isSearchable()) {
	          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
	          this.setPrepareStatement(preStat, ++j, searchKeyword);
	        }
	        rs = preStat.executeQuery();

//	        this.positionCursor(rs, startOffset, pageSize);

	        while (rs.next()) {
	          UserRecord tmpUserRecord = new UserRecord();
	          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
	          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
	          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
	          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
	          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
	          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
	          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
	          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
	          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
	          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
	          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
	          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
	          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
	          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
	          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
	          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
	          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
	          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
	          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
	          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
	          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
	          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
	          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
	          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
	          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
	          tmpUserRecord.setRecordCount(totalNumOfRecord);
	          tmpUserRecord.setRowNum(startOffset++);
	          ++rowLoopCnt;
	          result.add(tmpUserRecord);
	        }
	        return(result);
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
	        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
	      }
	    }
	  }

  
  public synchronized Integer getCountBySameLoginName(String loginName, String recordStatus) throws ApplicationException {
	    PreparedStatement preStat = null;
	    ResultSet rs = null;
	    StringBuffer sqlStat = new StringBuffer();
	    synchronized(dbConn) {
	        try {
	          sqlStat.append("SELECT  COUNT(*) AS NO_OF_ACTIVE_RECORDS ");
	          sqlStat.append("FROM   USER_RECORD A ");
	          sqlStat.append("WHERE  A.LOGIN_NAME LIKE ? ");
	          if (Utility.isEmpty(recordStatus)) {
	            sqlStat.append("ORDER BY A.UPDATE_DATE DESC ");
	          } else {
	            sqlStat.append("AND A.RECORD_STATUS= ? ");
	          }
	          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	          this.setPrepareStatement(preStat, 1, encryptLoginName(loginName)+"%");
	          this.setPrepareStatement(preStat,2,GlobalConstant.RECORD_STATUS_ACTIVE);
	          rs = preStat.executeQuery();
	          if (rs.next()) {
	            return getResultSetInteger(rs, "NO_OF_ACTIVE_RECORDS");
	          } else {
	            return(null);
	          }
	        } catch (ApplicationException appEx) {
	          throw appEx;
	        } catch (SQLException sqle) {
	          log.error(sqle, sqle);
	          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	        } catch (Exception e) {
	          log.error(e, e);
	          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	        } finally {
	          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	        }
	      }
  }
  
  public synchronized void deleteRelatedStaffRecord(Integer userid) throws ApplicationException {
    if (this.isSoftDeleteEnabled) {
      this.softDeleteRelatedStaffRecord(userid);
    }else {
      this.hardDeleteRelatedStaffRecord(userid);
    }
  }
  
  /**
   * Hard Delete Related Staff Record
   *
   * @param id    The staff record ID
   * @throws ApplicationException
   */
  private synchronized void hardDeleteRelatedStaffRecord(Integer userid) throws ApplicationException {
    
  }
  
  /**
   * Soft Delete Related Staff Record
   *
   * @param id    The staff record ID
   * @throws ApplicationException
   */
  private synchronized void softDeleteRelatedStaffRecord(Integer userid) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        /*sqlStat.append("UPDATE STAFF_RECORD A INNER JOIN USER_RECORD B ON A.ID=B.USER_LINK_ID ");
        sqlStat.append("SET A.RECORD_STATUS=?, A.UPDATE_COUNT=A.UPDATE_COUNT+1, A.UPDATE_DATE=?, A.UPDATER_ID=? ");
        sqlStat.append("WHERE B.ID=?");*///delete by TC for MSSQL did not support this way (STAFF_RECORD A INNER JOIN USER_RECORD B)
        sqlStat.append("UPDATE STAFF_RECORD ");
        sqlStat.append("SET RECORD_STATUS=?, UPDATE_COUNT=UPDATE_COUNT+1, UPDATE_DATE=?, UPDATER_ID=? ");
        sqlStat.append(" WHERE ID = (");
        sqlStat.append(" SELECT B.USER_LINK_ID FROM USER_RECORD B WHERE B.ID=? )");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_INACTIVE);
        this.setPrepareStatement(preStat, 2, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, 3, sessionContainer==null ? new Integer(0) : sessionContainer.getUserRecordID());
        this.setPrepareStatement(preStat, 4, userid);
        preStat.executeUpdate();
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /* Jicle.Wang 5309 2006/07/04 Start */
  /**
   * Soft Search and Sort for List User Mumber
   *
   * @param ListUserMemberForm    the form of ListUserMember
   * @throws ApplicationException
   */
  public synchronized List getGroupAndRecordByRoleID(ListUserMemberForm listUserForm ) throws ApplicationException{
   	List memberList = new ArrayList();
    int startOffset = TextUtility.parseInteger(listUserForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(listUserForm.getPageOffset());
  	PreparedStatement preStat = null;
  	 PreparedStatement preStatCnt1 = null;
  	 PreparedStatement preStatCnt2 = null;
      ResultSet rs = null;
      ResultSet rsCnt1 = null;
      ResultSet rsCnt2 = null;
      StringBuffer sqlStat1 = new StringBuffer();
      StringBuffer sqlStat2 = new StringBuffer();
      StringBuffer sqlStatOb = new StringBuffer();
      int totalNumOfAccountRecord = 0;
      int totalNumOfGroupRecord = 0;
      synchronized (dbConn) {
      	try{ 
      		// Search record for ListUserMumber from USER_RECORD and MTM_USER_RECORD_USER_ROLE
      		sqlStat1.append("SELECT A.ID AS ID, A.FULL_NAME AS memberName, '                             ' AS memberDesc, ")
      	         .append("A.UPDATER_ID AS updaterName, A.UPDATE_DATE AS updateDate, 'U' AS memberType ")
      	         .append("FROM  USER_RECORD A INNER JOIN MTM_USER_RECORD_USER_ROLE B ON A.ID=B.USER_RECORD_ID ")
      	         .append("WHERE  B.USER_ROLE_ID =? AND A.RECORD_STATUS = ? ");
      	  //append(" UNION ")
      		//Search record for ListUserMumber from USER_GROUP and MTM_USER_GROUP_USER_ROLE
      		sqlStat2.append("SELECT C.ID AS ID, C.GROUP_NAME AS memberName, C.GROUP_DESC AS memberDesc, ")
      	         .append("C.UPDATER_ID AS updaterName, C.UPDATE_DATE AS updateDate,'G' AS memberType ")
      	         .append("FROM  USER_GROUP C INNER JOIN MTM_USER_GROUP_USER_ROLE D ON C.ID=D.USER_GROUP_ID ")
      	         .append("WHERE  D.USER_ROLE_ID=? AND C.RECORD_STATUS = ? ");
      		//Add the SQL for Order
      		sqlStatOb.append(" ORDER BY ");
      		if(listUserForm.isSortable()){
      			sqlStatOb.append(listUserForm.getSortAttribute())
      			       .append(" ")
      			       .append(listUserForm.getSortOrder());
      		}
      		//For sum of sqlStat1   -- start
      		preStatCnt1 = dbConn.prepareStatement(this.getSelectCountSQL(sqlStat1).toString());
      		log.info("preStatCnt1:" + this.getSelectCountSQL(sqlStat1).toString());
      	  this.setPrepareStatement(preStatCnt1, 1, listUserForm.getUserRoleID());
      	  this.setPrepareStatement(preStatCnt1, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
      	  log.info("1:" + listUserForm.getUserRoleID());
      	  log.info("2:" + GlobalConstant.RECORD_STATUS_ACTIVE);
	        rsCnt1 = preStatCnt1.executeQuery();
	        if (rsCnt1.next()) {
	        	totalNumOfAccountRecord = rsCnt1.getInt(1);
	          log.info("rsCnt1.getInt(1):"+totalNumOfAccountRecord);
	        }
	        try { rsCnt1.close(); } catch (Exception ignore) {} finally { rsCnt1 = null; }
	        try { preStatCnt1.close(); } catch (Exception ignore) {} finally { preStatCnt1 = null; }
//	      For sum of sqlStat1   -- end
	        
//	      For sum of sqlStat2   -- start
	        preStatCnt2 = dbConn.prepareStatement(this.getSelectCountSQL(sqlStat2).toString());
	        log.info("preStatCnt2:" + this.getSelectCountSQL(sqlStat2).toString());
      	  this.setPrepareStatement(preStatCnt2, 1, listUserForm.getUserRoleID());
      	  this.setPrepareStatement(preStatCnt2, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
      	  log.info("1:" + listUserForm.getUserRoleID());
      	  log.info("2:" + GlobalConstant.RECORD_STATUS_ACTIVE);
	        rsCnt2 = preStatCnt2.executeQuery();
	        if (rsCnt2.next()) {
	        	totalNumOfGroupRecord = rsCnt2.getInt(1);
	        	log.info("rsCnt1.getInt(1):"+totalNumOfGroupRecord);
	        }
	        try { rsCnt2.close(); } catch (Exception ignore) {} finally { rsCnt2 = null; }
	        try { preStatCnt2.close(); } catch (Exception ignore) {} finally { preStatCnt2 = null; }
//	      For sum of sqlStat2   -- end
      		
      		sqlStat1.append(" UNION ").append(sqlStat2).append(sqlStatOb);
      		log.info("sql in getGroupAndRecordByRoleID:" + sqlStat1.toString());
      		//Add SQL for pe-page
      		sqlStat1 = this.getSelectListSQL(sqlStat1, startOffset, pageSize); 
      		preStat = dbConn.prepareStatement(sqlStat1.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          this.setPrepareStatement(preStat, 1, listUserForm.getUserRoleID());
          this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
          this.setPrepareStatement(preStat, 3, listUserForm.getUserRoleID());
          this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
          rs = preStat.executeQuery();
          while(rs.next()){
          	UserMember userMember=new UserMember();
            userMember.setID(getResultSetInteger(rs, "ID"));
            userMember.setMemberName(getResultSetString(rs, "memberName"));
            userMember.setMemberDesc(getResultSetString(rs, "memberDesc"));
            userMember.setMemberType(getResultSetString(rs, "memberType"));
            userMember.setUpdateDate(getResultSetTimestamp(rs, "updateDate"));
            userMember.setUpdaterName(UserInfoFactory.getUserFullName(getResultSetInteger(rs, "updaterName")));
            userMember.setRecordCount(totalNumOfAccountRecord+totalNumOfGroupRecord);//add the total to bean.
            userMember.setRowNum(startOffset++);  
            memberList.add(userMember);          	
          }

      	} catch (ApplicationException appEx) {
          throw appEx;
        } catch (SQLException sqle) {
          log.error(sqle, sqle);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
        } finally {
          try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }  	
  	return memberList;
    }
  /**
   * Soft Search and Sort for List User Mumber
   *
   * @param ListUserMemberForm    the form of ListUserMember
   * @throws ApplicationException
   */
    public synchronized List getRecordByRoleID(AbstractSearchForm form) throws ApplicationException{
	    int totalNumOfRecord = 0;
	    int rowLoopCnt = 0;
    	List memberList = new ArrayList();
    	ListUserMemberForm listUserForm=(ListUserMemberForm)form;
      int startOffset = TextUtility.parseInteger(listUserForm.getCurStartRowNo());
      int pageSize = TextUtility.parseInteger(listUserForm.getPageOffset());
      PreparedStatement preStatCnt = null;
    	PreparedStatement preStat = null;
      ResultSet rs = null;
      ResultSet rsCnt = null;
      
      StringBuffer sqlStat = new StringBuffer();
      synchronized (dbConn) {
        	try{    		
        		sqlStat.append("SELECT A.ID AS ID, A.FULL_NAME AS memberName, '                             ' AS memberDesc, ")
        	         .append("A.UPDATER_ID AS updaterName, A.UPDATE_DATE AS updateDate ,'U' AS memberType ")
        	         .append("FROM  USER_RECORD A INNER JOIN MTM_USER_RECORD_USER_GROUP B ON A.ID=B.USER_RECORD_ID ")
        	         .append("WHERE  B.USER_GROUP_ID=? AND A.RECORD_STATUS = ? ")
        		       .append(" ORDER BY ");
        		if(listUserForm.isSortable()){
        			sqlStat.append(listUserForm.getSortAttribute())
        			       .append(" ")
        			       .append(listUserForm.getSortOrder());
        		}
        		
        		String sqlStatCnt = this.getSelectCountSQL(sqlStat).toString();
        		preStatCnt = dbConn.prepareStatement(sqlStatCnt);
        	  this.setPrepareStatement(preStatCnt, 1, listUserForm.getUserGroupID());
        	  this.setPrepareStatement(preStatCnt, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        	  
  	        rsCnt = preStatCnt.executeQuery();
  	        if (rsCnt.next()) {
  	          totalNumOfRecord = rsCnt.getInt(1);
  	        }
  	        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
  	        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
        		
        		sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize); 
        		log.info("mysql in getRecordByRoleID:"+sqlStat.toString());
        		preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            this.setPrepareStatement(preStat, 1, listUserForm.getUserGroupID());
            this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
            log.info("parameter 1:" + listUserForm.getUserGroupID());
            log.info("parameter 2:" + GlobalConstant.RECORD_STATUS_ACTIVE);
            rs = preStat.executeQuery();
            while(rs.next()){
            	UserMember userMember=new UserMember();
              userMember.setID(getResultSetInteger(rs, "ID"));
              userMember.setMemberName(getResultSetString(rs, "memberName"));
              userMember.setMemberDesc(getResultSetString(rs, "memberDesc"));
              userMember.setMemberType(UserMember.MEMBER_TYPE_USER);
              userMember.setUpdateDate(getResultSetTimestamp(rs, "updateDate"));
              userMember.setUpdaterName(UserInfoFactory.getUserFullName(getResultSetInteger(rs, "updaterName")));
              userMember.setRecordCount(totalNumOfRecord);
              userMember.setRowNum(startOffset++);
              memberList.add(userMember);          	
            }
        	} catch (ApplicationException appEx) {
            throw appEx;
          } catch (SQLException sqle) {
            log.error(sqle, sqle);
            throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
          } catch (Exception e) {
            log.error(e, e);
            throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
          } finally {
            try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
            try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
          }
        }  	
    	return memberList;
      }
    /* Jicle.Wang 5309 2006/07/04 End   */
    
    /***
     * autor:barbin 20060720
     * function:this method solve anaysis for lutos email address
     */
    public synchronized AbstractBaseObject getUserByEmailAddressForLutos(String emailAddress) throws ApplicationException {
	    PreparedStatement preStat = null;
	    ResultSet rs = null;
	    StringBuffer sqlStat = new StringBuffer();

	    synchronized(dbConn) {
	      try {
	        sqlStat.append("SELECT A.ID, A.LOGIN_NAME, A.LOGIN_PWD, A.FIRST_NAME, A.LAST_NAME, A.FULL_NAME, A.EMAIL, A.FAILED_ATTEMPT, A.MAX_ATTEMPT, A.PWD_EXPIRY_DAY, A.LAST_PWD_UPDATE_DATE, A.STATUS, A.USER_TYPE, A.USER_LINK_ID, A.PREFERENCE, A.LOCALE, A.USER_DEF_1, A.USER_DEF_2, A.USER_DEF_3, A.USER_DEF_4, A.USER_DEF_5, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.MODIFY_LOGIN_PWD ");
	        sqlStat.append("FROM   USER_RECORD A ");
	        sqlStat.append("WHERE  A.EMAIL like ? AND A.RECORD_STATUS = ? ");
	        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        this.setPrepareStatement(preStat, 1, emailAddress+"%");
	        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
	        rs = preStat.executeQuery();
	        if (rs.next()) {
	          UserRecord tmpUserRecord = new UserRecord();
	          tmpUserRecord.setID(getResultSetInteger(rs, "ID"));
	          tmpUserRecord.setLoginName(decryptLoginName(getResultSetString(rs, "LOGIN_NAME")));
	          tmpUserRecord.setLoginPwd(getResultSetString(rs, "LOGIN_PWD"));
	          tmpUserRecord.setFirstName(getResultSetString(rs, "FIRST_NAME"));
	          tmpUserRecord.setLastName(getResultSetString(rs, "LAST_NAME"));
	          tmpUserRecord.setFullName(getResultSetString(rs, "FULL_NAME"));
	          tmpUserRecord.setEmail(getResultSetString(rs, "EMAIL"));
	          tmpUserRecord.setFailedAttempt(getResultSetInteger(rs, "FAILED_ATTEMPT"));
	          tmpUserRecord.setMaxAttempt(getResultSetInteger(rs, "MAX_ATTEMPT"));
	          tmpUserRecord.setPwdExpiryDay(getResultSetInteger(rs, "PWD_EXPIRY_DAY"));
	          tmpUserRecord.setLastPwdUpdateDate(getResultSetTimestamp(rs, "LAST_PWD_UPDATE_DATE"));
	          tmpUserRecord.setStatus(getResultSetString(rs, "STATUS"));
	          tmpUserRecord.setUserType(getResultSetString(rs, "USER_TYPE"));
	          tmpUserRecord.setUserLinkID(getResultSetInteger(rs, "USER_LINK_ID"));
	          tmpUserRecord.setPreference(getResultSetInteger(rs, "PREFERENCE"));
	          tmpUserRecord.setLocale(getResultSetString(rs, "LOCALE"));
	          tmpUserRecord.setUserDef1(getResultSetString(rs, "USER_DEF_1"));
	          tmpUserRecord.setUserDef2(getResultSetString(rs, "USER_DEF_2"));
	          tmpUserRecord.setUserDef3(getResultSetString(rs, "USER_DEF_3"));
	          tmpUserRecord.setUserDef4(getResultSetString(rs, "USER_DEF_4"));
	          tmpUserRecord.setUserDef5(getResultSetString(rs, "USER_DEF_5"));
	          tmpUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
	          tmpUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
	          tmpUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
	          tmpUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
	          tmpUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
	          tmpUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
	          tmpUserRecord.setModifyLoginPwd(getResultSetString(rs,"MODIFY_LOGIN_PWD"));
	          tmpUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpUserRecord.getCreatorID()));
	          tmpUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpUserRecord.getUpdaterID()));
	          return(tmpUserRecord);
	        } else {
	          return null;
	        }
	      } catch (ApplicationException appEx) {
	        throw appEx;
	      } catch (SQLException sqle) {
	        log.error(sqle, sqle);
	        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
	      } catch (Exception e) {
	        log.error(e, e);
	        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
	      } finally {
	        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
	        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
	      }
	    }
	} 
  
  /**
   * Account the value of user can execute current step.
   * 
   * @param allPermission the permission definitions in a text
   * @return the result count, zero if none of user.
   * @throws ApplicationException
   */
  public synchronized int getUserCountCanExecute(String allPermission) throws ApplicationException {
    
    String permissionData = TextUtility.replaceString(allPermission, "\r", "");
    String[] permissionValues = TextUtility.splitString(permissionData, "\n");
    
    Set userIDsSpecified = new HashSet();
    Set userIDsSpecified2Execute = new HashSet();
    Set userIDsSpecified2Deny = new HashSet();
    Set userIDsInRoles = new HashSet();
    Set userIDsInRoles2Execute = new HashSet();
    Set userIDsInRoles2Deny = new HashSet();
    Set userIDsInGroups = new HashSet();
    Set userIDsInGroups2Execute = new HashSet();
    Set userIDsInGroups2Deny = new HashSet();
    
    boolean haveEveryoneDefinition = false;
    boolean isEveryoneCanExecute = true; // If true, result = total user - deny user, else, result = total allow user.
    
    for (int i = 0; i < permissionValues.length; i++) {
      
      if (Utility.isEmpty(permissionValues[i])) {
        continue;
      }
      
      String[] itemPermissionValues = TextUtility.splitString(permissionValues[i], "\t");
      
      String currentID = itemPermissionValues[0];
      String currentUserType = itemPermissionValues[2];
      String currentPermission = itemPermissionValues[3];
      
      if (currentPermission.indexOf(PermissionManager.DENIED_EXECUTE_PERMISSION) < 0
          && currentPermission.indexOf(PermissionManager.EXECUTE_PERMISSION) < 0) {
        continue; // Ignore current check if none of execute definition.
      }
      
      // Type of user.
      if (MtmWorkflowStepUserActor.ACTOR_TYPE_USER.equals(currentUserType)) {
        
        if (userIDsSpecified2Deny.contains(currentID)) {
          continue;
        }
        
        userIDsInGroups2Execute.remove(currentID);
        userIDsInGroups2Deny.remove(currentID);
        userIDsInRoles2Execute.remove(currentID);
        userIDsInRoles2Deny.remove(currentID);
        
        userIDsSpecified.add(currentID);
        
        if (currentPermission.indexOf(PermissionManager.EXECUTE_PERMISSION) > -1) {
          userIDsSpecified2Execute.add(currentID);
        }
        if (currentPermission.indexOf(PermissionManager.DENIED_EXECUTE_PERMISSION) > -1) {
          userIDsSpecified2Execute.remove(currentID);
          userIDsSpecified2Deny.add(currentID);
        }
      }
      
      // Type of special user (dynamic user such as 'V' 'K' etc.)
      // Ignore because this type of users is dynamic.
      
      // Type of role.
      if (MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE.equals(currentUserType)) {
        
        Set usersInRole = this.getUserIdsOfRole(currentID);
        
        if (usersInRole == null) {
          continue;
        }
        
        for (Iterator iter = usersInRole.iterator(); iter.hasNext();) {
          String userInner = iter.next().toString();
          
          if (userIDsSpecified.contains(userInner)) {
            continue;
          }
          
          if (userIDsInRoles2Deny.contains(userInner)) {
            continue;
          }
          
          userIDsInGroups2Execute.remove(userInner);
          userIDsInGroups2Deny.remove(userInner);
          
          userIDsInRoles.add(userInner);
          if (currentPermission.indexOf(PermissionManager.EXECUTE_PERMISSION) > -1) {
            userIDsInRoles2Execute.add(userInner);
          }
          if (currentPermission.indexOf(PermissionManager.DENIED_EXECUTE_PERMISSION) > -1) {
            userIDsInRoles2Execute.remove(userInner);
            userIDsInRoles2Deny.add(userInner);
          }
        }
      }
      
      // Type of group.
      if (MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP.equals(currentUserType)) {
        
        Set usersInGroup = this.getUserIdsInGroup(currentID);
        
        if (usersInGroup == null) {
          continue;
        }
        
        for (Iterator iter = usersInGroup.iterator(); iter.hasNext();) {
          String userInner = iter.next().toString();
          
          if (userIDsSpecified.contains(userInner) || userIDsInRoles.contains(userInner)) {
            continue;
          }
          
          if (userIDsInGroups2Deny.contains(userInner)) {
            continue;
          }
          
          userIDsInGroups.add(userInner);
          if (currentPermission.indexOf(PermissionManager.EXECUTE_PERMISSION) > -1) {
            userIDsInGroups2Execute.add(userInner);
          }
          if (currentPermission.indexOf(PermissionManager.DENIED_EXECUTE_PERMISSION) > -1) {
            userIDsInGroups2Execute.remove(userInner);
            userIDsInGroups2Deny.add(userInner);
          }
        }
      }
      
      // Type of every one.
      if (MtmWorkflowStepUserActor.ACTOR_TYPE_OTHER.equals(currentUserType)
          && MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE.equals(currentID)) {
        
        haveEveryoneDefinition = true;

        if (isEveryoneCanExecute && currentPermission.indexOf(PermissionManager.DENIED_EXECUTE_PERMISSION) > -1) {
          isEveryoneCanExecute = false;
        }
      }
      
    }
    
    if (haveEveryoneDefinition && isEveryoneCanExecute) {
      int totalUserCount = this.getTotalUserCount();
      int totalDenyUserCount = userIDsSpecified2Deny.size() + userIDsInRoles2Deny.size() + userIDsInGroups2Deny.size();
      return (totalUserCount - totalDenyUserCount);
    }
    
    return (userIDsSpecified2Execute.size() + userIDsInRoles2Execute.size() + userIDsInGroups2Execute.size());
  }
  
  /**
   * Get total effective user count, not contain the system administrator.
   * 
   * @return the total effective user count.
   * @throws ApplicationException
   */
  public int getTotalUserCount() throws ApplicationException {
    
    PreparedStatement countStat = null;
    ResultSet countRS = null;
    
    String countQueryStr = "SELECT COUNT(ID) TOTAL_COUNT FROM USER_RECORD WHERE STATUS = ? AND ID <> 0";
    
    try {
      countStat = dbConn.prepareStatement(countQueryStr);
      this.setPrepareStatement(countStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
      
      countRS = countStat.executeQuery();
      while (!Utility.isEmpty(countRS) && countRS.next()) {
        return countRS.getInt("TOTAL_COUNT");
      }
      return 0;
    } catch (SQLException e) {
      log.error("Error occurs!", e);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
    }
    finally {
      try {
        countRS.close();
        countStat.close();
      } catch (Exception ignore) { 
      } finally {
        countRS = null;
        countStat = null;
      }
    }
  }
  
    /**
     * Check the rule for the step.If the value of the rule is smaller than the
     * value that can execute the step current.
     * 
     * @param step
     * @deprecated
     */
  /*
  public synchronized int getUserCountCanExecute(String allPermision) throws ApplicationException {
    String permData = TextUtility.replaceString(allPermision, "\r", "");
    String[] tmpAry = TextUtility.splitString(permData, "\n");
    Set userIDsCanExecute = new HashSet();
    if (tmpAry == null) {
      tmpAry = new String[0];
    }
    // specific user account
    for (int index = 0; index < tmpAry.length; index++) {
      if (!Utility.isEmpty(tmpAry[index])) {
        String[] rowAry = TextUtility.splitString(tmpAry[index], "\t");
        // can execute
        if (rowAry[3].indexOf("W") != -1) {
          // get the users of the group
          if (MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP.equals(rowAry[2])) {
            userIDsCanExecute.addAll(getUserIdsInGroup(rowAry[0]));
          }
          // get the users of the role
          else if (MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE.equals(rowAry[2])) {
            userIDsCanExecute.addAll(getUserIdsOfRole(rowAry[0]));
          } else if (MtmWorkflowStepUserActor.ACTOR_TYPE_USER.equals(rowAry[2])) {
            userIDsCanExecute.add(new Integer(rowAry[0]));
          }
          // if everyone can execute,ignore
          else if (MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE.equals(rowAry[0])) {
            return Integer.MAX_VALUE;
          }
        }
      }
    }
    // bill add for EIP-1420 start
	for (int index = 0; index < tmpAry.length; index++) {
	  if (!Utility.isEmpty(tmpAry[index])) {
	    String[] rowAry = TextUtility.splitString(tmpAry[index], "\t");
	    Pattern pattern = Pattern.compile("[0-9]{1,}");
	    Matcher matcher = pattern.matcher((CharSequence) rowAry[0]);
	    boolean flag = matcher.matches();
	    if (flag) {
	      if (rowAry[3].indexOf("W") == -1 && userIDsCanExecute.contains(new Integer(rowAry[0]))) {
		    userIDsCanExecute.remove(new Integer(rowAry[0]));
		  }
	    }
	  }
    }
	//bill add for EIP-1420 end
    return userIDsCanExecute.size();
  }
  */

  /**
   * get the user's IDs of the role
   * 
   * @param roleId
   * @return
   * @throws ApplicationException
   */
  private synchronized Set getUserIdsOfRole(String roleId) throws ApplicationException {
    Set result = new HashSet();
    synchronized (dbConn) {
      PreparedStatement preStat = null;
      ResultSet rs = null;
      try {
        String sql = "select USER_RECORD_ID from MTM_USER_RECORD_USER_ROLE where USER_ROLE_ID=? and RECORD_STATUS='"+GlobalConstant.RECORD_STATUS_ACTIVE+"'";

        preStat = dbConn
            .prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        preStat.setInt(1, Integer.valueOf(roleId).intValue());
        rs = preStat.executeQuery();
        while (rs.next()) {
          result.add(new Integer(rs.getInt("USER_RECORD_ID")));
        }
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }
    return result;
  }

  /**
   * get the user's IDs of the group
   * 
   * @param groupId
   * @return
   * @throws ApplicationException
   */
  private synchronized Set getUserIdsInGroup(String groupId) throws ApplicationException {
    Set result = new HashSet();
    synchronized (dbConn) {
      PreparedStatement preStat = null;
      ResultSet rs = null;
      try {
        String sql = "select USER_RECORD_ID from MTM_USER_RECORD_USER_GROUP where USER_GROUP_ID=? and RECORD_STATUS='"+GlobalConstant.RECORD_STATUS_ACTIVE+"'";

        preStat = dbConn
            .prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        preStat.setInt(1, Integer.valueOf(groupId).intValue());
        rs = preStat.executeQuery();
        while (rs.next()) {
          result.add(new Integer(rs.getInt("USER_RECORD_ID")));
        }
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }
    return result;
  }
  
  
  //add 
  public synchronized List getResultList(String columnName) {
    List result = new ArrayList();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    try {
      synchronized (dbConn) {
        sqlStat.append("SELECT  L.ID, RECORD_TYPE, FULL_NAME, E_MAIL FROM    ( SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL FROM   USER_RECORD U LEFT   JOIN STAFF_RECORD S ON S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' WHERE  U.ID <> 0 UNION SELECT G.GROUP_NAME, G.ID, 'G', NULL FROM   USER_GROUP G WHERE  G.RECORD_STATUS = 'A' UNION SELECT R.ROLE_NAME, R.ID, 'R', NULL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ) L ORDER BY L.FULL_NAME");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        rs = preStat.executeQuery();
        while(rs.next()){
          result.add(rs.getString(columnName));
        }
        return result;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
    } catch (Exception e) {
      log.error(e, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
    return null;
  }

  public synchronized List getResultList() {
    List result = new ArrayList();
    PreparedStatement preStat = null;
    PreparedStatement preStat2 = null;
    ResultSet rs = null;
    ResultSet rs2 = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStat2 = new StringBuffer();
    List column_Id = new ArrayList();
    List column_Record_Type = new ArrayList();
    List column_Full_Name = new ArrayList();
    List column_Email = new ArrayList();
    List column_Id_History = new ArrayList();
    List column_Record_Type_History = new ArrayList();
    List column_Full_Name_History = new ArrayList();
    List column_Email_History = new ArrayList();
    
    try {
      synchronized (dbConn) {
        sqlStat.append("SELECT  L.ID, RECORD_TYPE, FULL_NAME, E_MAIL FROM    ( SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL FROM   USER_RECORD U LEFT   JOIN STAFF_RECORD S ON S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' WHERE  U.ID <> 0 UNION SELECT G.GROUP_NAME, G.ID, 'G', NULL FROM   USER_GROUP G WHERE  G.RECORD_STATUS = 'A' UNION SELECT R.ROLE_NAME, R.ID, 'R', NULL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ) L ORDER BY L.FULL_NAME");
        //sqlStat.append("  SELECT DISTINCT W.ID,W.RECORD_TYPE,W.FULL_NAME,W.E_MAIL FROM   (         SELECT  H.ID, H.RECORD_TYPE, H.FULL_NAME, H.E_MAIL         FROM   (                     SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE                 FROM   (                         SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_RECORD U                                ON U.ID = I.OBJECT_ID                         LEFT   JOIN STAFF_RECORD S                                 ON S.ID = U.USER_LINK_ID                                 AND S.RECORD_STATUS = 'A'                          WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'U'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                         UNION                         SELECT G.GROUP_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_GROUP G                                ON G.ID = I.OBJECT_ID                         WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'G'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                         UNION                         SELECT R.ROLE_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_ROLE R                                ON R.ID = I.OBJECT_ID                         WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'R'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + " ) L                 ORDER BY L.ACT_DATE DESC, L.FULL_NAME ) H         UNION         SELECT R.ID, R.RECORD_TYPE, R.FULL_NAME, R.E_MAIL         FROM   (                  SELECT  L.ID, L.RECORD_TYPE, L.FULL_NAME, L.E_MAIL                   FROM    (                              SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL                              FROM   USER_RECORD U                              LEFT   JOIN STAFF_RECORD S                                     ON S.ID = U.USER_LINK_ID                                     AND S.RECORD_STATUS = 'A'                              WHERE  U.ID <> 0                              UNION                              SELECT G.GROUP_NAME, G.ID, 'G', NULL                              FROM   USER_GROUP G                              WHERE  G.RECORD_STATUS = 'A'                              UNION                              SELECT R.ROLE_NAME, R.ID, 'R', NULL                              FROM   USER_ROLE R                              WHERE  R.RECORD_STATUS = 'A' ) L                  ORDER BY L.FULL_NAME                ) R         ) W ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        rs = preStat.executeQuery();
        while(rs.next()){
          column_Id.add(rs.getString(this.ID));
          column_Record_Type.add(rs.getString(this.RECORD_TYPE));
          column_Full_Name.add(rs.getString(this.FULL_NAME));
          column_Email.add(rs.getString(this.E_MAIL));
        }
        result.add(column_Id);
        result.add(column_Record_Type);
        result.add(column_Full_Name);
        result.add(column_Email);
        //sqlStat2.append("SELECT R.FULL_NAME, R.ID, R.RECORD_TYPE, R.E_MAIL, R.ACT_DATE FROM ( SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE FROM   (               SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE             FROM   INVITEE_PICKER_HISTORY I              LEFT   JOIN USER_RECORD U                            ON U.ID = I.OBJECT_ID              LEFT   JOIN STAFF_RECORD S                             ON S.ID = U.USER_LINK_ID                              AND S.RECORD_STATUS = 'A'              WHERE  I.RECORD_STATUS = 'A'                             AND I.OBJECT_TYPE = 'U'                             AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "               UNION               SELECT G.GROUP_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE               FROM   INVITEE_PICKER_HISTORY I              LEFT   JOIN USER_GROUP G                             ON G.ID = I.OBJECT_ID              WHERE  I.RECORD_STATUS = 'A'                             AND I.OBJECT_TYPE = 'G'                             AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "               UNION               SELECT R.ROLE_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE              FROM   INVITEE_PICKER_HISTORY I              LEFT   JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID              WHERE  I.RECORD_STATUS = 'A'                             AND I.OBJECT_TYPE = 'R'                             AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + " ) L ) R GROUP BY R.ID ORDER BY R.ACT_DATE DESC, R.FULL_NAME ");
        sqlStat2.append("  SELECT R.FULL_NAME, R.ID, R.RECORD_TYPE, R.E_MAIL FROM   ( SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE           FROM   ( SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE                                FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_RECORD U  ON U.ID = I.OBJECT_ID                                 LEFT   JOIN STAFF_RECORD S  ON S.ID = U.USER_LINK_ID  AND S.RECORD_STATUS = 'A'                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'U'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                             AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'U'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   UNION                                  SELECT G.GROUP_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                                  FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_GROUP G                             ON G.ID = I.OBJECT_ID                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'G'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                      AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'G'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   UNION                                  SELECT R.ROLE_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                                 FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'R'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                           AND I.ACT_DATE = (  SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'R'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   ) L           ) R  ORDER BY R.ACT_DATE DESC, R.FULL_NAME ");
        preStat2 = dbConn.prepareStatement(sqlStat2.toString());
        rs2 = preStat2.executeQuery();
        while(rs2.next()){
          column_Id_History.add(rs2.getString(this.ID));
          column_Record_Type_History.add(rs2.getString(this.RECORD_TYPE));
          column_Full_Name_History.add(rs2.getString(this.FULL_NAME));
          column_Email_History.add(rs2.getString(this.E_MAIL));
        }
        result.add(column_Id_History);
        result.add(column_Record_Type_History);
        result.add(column_Full_Name_History);
        result.add(column_Email_History);
        return result;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
    } catch (Exception e) {
      log.error(e, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
    return null;
  }

  public synchronized List getUserRecordsAndPageInfo(
      String searchUserName ,
      String searchType , 
      String searchKey , 
      String searchHierarchy , 
      String startOffset , 
      String pageSize , 
      String orderBy ,
      String req ) {
    List result = new ArrayList();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = null;
    String cnt = null;
    
    if (Utility.isEmpty(orderBy)){
      orderBy = this.FULL_NAME;
      req = "ASC";
    }
    if (this.TYPE_HIERARCHY.equals(searchType) && Utility.isEmpty(searchKey)){
      searchType = this.TYPE_GROUP;
    }
    
    try {
      synchronized (dbConn) {
        sqlStat.append(" SELECT  U.ID ID, U.FULL_NAME, S.EMAIL E_MAIL, S.TITLE, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_1_ID = H.ID ) LVL1_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_2_ID = H.ID ) LVL2_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_3_ID = H.ID ) LVL3_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_4_ID = H.ID ) LVL4_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_5_ID = H.ID ) LVL5_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_6_ID = H.ID ) LVL6_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_7_ID = H.ID ) LVL7_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_8_ID = H.ID ) LVL8_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_9_ID = H.ID ) LVL9_NAME, ( SELECT H.NAME  FROM   STAFF_HIERARCHY H  WHERE  S.LEVEL_10_ID = H.ID ) LVL10_NAME ");
        sqlStat.append(" FROM   USER_RECORD U ,STAFF_RECORD S ");
        if (!Utility.isEmpty(searchKey)) {
          if (this.TYPE_GROUP.equals(searchType)) {
            sqlStat.append(" ,(SELECT DISTINCT A.USER_RECORD_ID  FROM   MTM_USER_RECORD_USER_GROUP A, USER_RECORD B  WHERE  A.USER_GROUP_ID = " + searchKey + "  AND    A.RECORD_STATUS = 'A'  AND    A.USER_RECORD_ID = B.ID  AND    B.RECORD_STATUS = 'A' ) G");
          }  else if (this.TYPE_ROLE.equals(searchType)) {
            sqlStat.append(" ,(SELECT DISTINCT A.USER_RECORD_ID  FROM   MTM_USER_RECORD_USER_ROLE A, USER_RECORD B  WHERE  A.USER_ROLE_ID = " + searchKey + "  AND    A.RECORD_STATUS = 'A'  AND    A.USER_RECORD_ID = B.ID  AND    B.RECORD_STATUS = 'A' ) G");
          } else if (this.TYPE_HIERARCHY.equals(searchType)) {
            //do nothing
          }
        } else {
          //do nothing
        }
        sqlStat.append(" WHERE  U.ID <> 0 AND U.RECORD_STATUS = 'A' AND U.STATUS = 'A' AND S.ID = U.USER_LINK_ID ");
        if (!Utility.isEmpty(searchUserName)) {
          sqlStat.append(" AND U.FULL_NAME LIKE '%" + searchUserName + "%' ");
        }
        if (!Utility.isEmpty(searchKey)) {
          if (this.TYPE_GROUP.equals(searchType)) {
            sqlStat.append(" AND  U.ID = G.USER_RECORD_ID ");
          } else if (this.TYPE_ROLE.equals(searchType)) {
            sqlStat.append(" AND  U.ID = G.USER_RECORD_ID ");
          } else if (this.TYPE_HIERARCHY.equals(searchType)) {
            //do nothing
          }
        }
        
        if (this.TYPE_HIERARCHY.equals(searchType)){
          StringBuffer sbResult = new StringBuffer("SELECT * FROM ");
          sbResult.append("( " + sqlStat.toString() + " ) U ");
          //sbResult.append(" WHERE U." + searchHierarchy + "='" + searchKey + "' ");
          sbResult.append(" WHERE U." + searchHierarchy + "= (SELECT NAME FROM STAFF_HIERARCHY WHERE ID = '" + searchKey + "') ");
          sqlStat = sbResult;
        }
        
        sqlStatCnt = new StringBuffer("SELECT COUNT(*) FROM ");
        sqlStatCnt.append("(" + sqlStat.toString() + ") CNT");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        rsCnt = preStatCnt.executeQuery();
        while(rsCnt.next()){
          cnt = rsCnt.getString(1);
        }
        try { 
          rsCnt.close(); 
        } catch (Exception ignore) {
          log.error(ignore);
        } finally { 
          rsCnt = null; 
        }
        try { 
          preStatCnt.close(); 
        } catch (Exception ignore) {
          log.error(ignore);
        } finally { 
          preStatCnt = null; 
        }

        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_MSSQL){
          sqlStat.insert(sqlStat.indexOf("SELECT") + 6, " TOP " + (Integer.parseInt(startOffset) + Integer.parseInt(pageSize) - 1));
          sqlStat.append(" AND U.ID NOT IN ( ");
          
          sqlStat.append(" SELECT TOP " + (Integer.parseInt(startOffset)-1) + " U.ID ID ");
          sqlStat.append(" FROM   USER_RECORD U ,STAFF_RECORD S ");
          if (!Utility.isEmpty(searchKey)) {
            if (this.TYPE_GROUP.equals(searchType)) {
              sqlStat.append(" ,(SELECT DISTINCT A.USER_RECORD_ID  FROM   MTM_USER_RECORD_USER_GROUP A, USER_RECORD B  WHERE  A.USER_GROUP_ID = " + searchKey + "  AND    A.RECORD_STATUS = 'A'  AND    A.USER_RECORD_ID = B.ID  AND    B.RECORD_STATUS = 'A' ) G");
            }  else if (this.TYPE_ROLE.equals(searchType)) {
              sqlStat.append(" ,(SELECT DISTINCT A.USER_RECORD_ID  FROM   MTM_USER_RECORD_USER_ROLE A, USER_RECORD B  WHERE  A.USER_ROLE_ID = " + searchKey + "  AND    A.RECORD_STATUS = 'A'  AND    A.USER_RECORD_ID = B.ID  AND    B.RECORD_STATUS = 'A' ) G");
            } else if (this.TYPE_HIERARCHY.equals(searchType)) {
              //do nothing
            }
          } else {
            //do nothing
          }
          sqlStat.append(" WHERE  U.ID <> 0 AND U.RECORD_STATUS = 'A'  AND S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' ");
          if (!Utility.isEmpty(searchUserName)) {
            sqlStat.append(" AND U.FULL_NAME LIKE '%" + searchUserName + "%' ");
          }
          if (!Utility.isEmpty(searchKey)) {
            if (this.TYPE_GROUP.equals(searchType)) {
              sqlStat.append(" AND U.ID = G.USER_RECORD_ID ");
            } else if (this.TYPE_ROLE.equals(searchType)) {
              sqlStat.append(" AND U.ID = G.USER_RECORD_ID ");
            } else if (this.TYPE_HIERARCHY.equals(searchType)) {
              //do nothing
            }
          }
          sqlStat.append(" ) ");
          if (this.TYPE_HIERARCHY.equals(searchType)){
            StringBuffer sbResult = new StringBuffer("SELECT * FROM ");
            sbResult.append("( " + sqlStat.toString() + " ) U ");
            //sbResult.append(" WHERE U." + searchHierarchy + "='" + searchKey + "' ");
            sbResult.append(" WHERE U." + searchHierarchy + "= (SELECT NAME FROM STAFF_HIERARCHY WHERE ID = '" + searchKey + "') ");
            sqlStat = sbResult;
          }
        }
        
        sqlStat.append(" ORDER BY U." + orderBy + " " + req + " ");
        
        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_MSSQL){
        } else {
          sqlStat = this.getSelectListSQL(sqlStat,Integer.parseInt(startOffset),Integer.parseInt(pageSize));
        }
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        ///*
        rs = preStat.executeQuery();
        int rowLoopCnt = 0;
        while(rs.next() && rowLoopCnt < Integer.parseInt(pageSize)){
          List tempRecord = new ArrayList();
          tempRecord.add(rs.getString(this.ID));
          tempRecord.add(rs.getString(this.FULL_NAME));
          tempRecord.add(rs.getString(this.E_MAIL));
          tempRecord.add(rs.getString(this.TITLE));
          tempRecord.add(rs.getString(this.LVL1_NAME));
          tempRecord.add(rs.getString(this.LVL2_NAME));
          tempRecord.add(rs.getString(this.LVL3_NAME));
          tempRecord.add(rs.getString(this.LVL4_NAME));
          tempRecord.add(rs.getString(this.LVL5_NAME));
          tempRecord.add(rs.getString(this.LVL6_NAME));
          tempRecord.add(rs.getString(this.LVL7_NAME));
          tempRecord.add(rs.getString(this.LVL8_NAME));
          tempRecord.add(rs.getString(this.LVL9_NAME));
          tempRecord.add(rs.getString(this.LVL10_NAME));
          ++rowLoopCnt;
          result.add(tempRecord);
        }
        //*/
        StringBuffer pageInfomation = new StringBuffer();
        pageInfomation.append("startOffset:" + startOffset + ";");
        pageInfomation.append("pageSize:" + pageSize + ";");
        pageInfomation.append("orderBy:" + orderBy + ";");
        pageInfomation.append("req:" + req + ";");
        pageInfomation.append("total:" + cnt + "");
        result.add(pageInfomation.toString());
        return result;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
    } catch (Exception e) {
      log.error(e, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
    return null;
  }
  
  public synchronized List getResultListDynamic(
      boolean enableUser,
      boolean enableGroup,
      boolean enableRole,
      Integer choices,
      String key,
      ArrayList expectUserIDs,
      ArrayList expectGroupIDs,
      ArrayList expectRoleIDs){
    List result = new ArrayList();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List column_Id = new ArrayList();
    List column_Record_Type = new ArrayList();
    List column_Full_Name = new ArrayList();
    List column_Email = new ArrayList();
    List column_Id_History = new ArrayList();
    List column_Record_Type_History = new ArrayList();
    List column_Full_Name_History = new ArrayList();
    List column_Email_History = new ArrayList();
    int i=0;
    try{
      synchronized (dbConn) {
        if (Utility.isEmpty(key)){//search history
          //sqlStat.append("  SELECT R.FULL_NAME, R.ID, R.RECORD_TYPE, R.E_MAIL FROM   ( SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE           FROM   ( SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE                                FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_RECORD U  ON U.ID = I.OBJECT_ID                                 LEFT   JOIN STAFF_RECORD S  ON S.ID = U.USER_LINK_ID  AND S.RECORD_STATUS = 'A'                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'U'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                             AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'U'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   UNION                                  SELECT G.GROUP_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                                  FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_GROUP G                             ON G.ID = I.OBJECT_ID                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'G'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                      AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'G'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   UNION                                  SELECT R.ROLE_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                                 FROM   INVITEE_PICKER_HISTORY I                                 LEFT   JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID                                 WHERE  I.RECORD_STATUS = 'A'                                                       AND I.OBJECT_TYPE = 'R'                                                       AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                           AND I.ACT_DATE = (  SELECT MAX(TI.ACT_DATE)                                 FROM   INVITEE_PICKER_HISTORY TI                                        WHERE  TI.RECORD_STATUS = 'A'                                                            AND TI.OBJECT_TYPE = 'R'                                                            AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                                 AND TI.OBJECT_ID = I.OBJECT_ID)                   ) L           ) R  ORDER BY R.ACT_DATE DESC, R.FULL_NAME ");

          sqlStat.append("SELECT R.FULL_NAME, R.ID, R.RECORD_TYPE, R.E_MAIL ");
          sqlStat.append("FROM   ( ");
          sqlStat.append("       SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE           ");
          sqlStat.append("       FROM   ( ");
          if (enableUser){
            sqlStat.append("                SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE    FROM   INVITEE_PICKER_HISTORY I      JOIN USER_RECORD U  ON U.ID = I.OBJECT_ID AND U.RECORD_STATUS = 'A'  LEFT   JOIN STAFF_RECORD S  ON S.ID = U.USER_LINK_ID  AND S.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'   AND I.OBJECT_TYPE = 'U'  AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (  SELECT MAX(TI.ACT_DATE)     FROM   INVITEE_PICKER_HISTORY TI   WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'U' AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID ) ");
            if (enableGroup) {
              sqlStat.append("      UNION ");
              sqlStat.append("                SELECT G.GROUP_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE  FROM   INVITEE_PICKER_HISTORY I     JOIN USER_GROUP G                             ON G.ID = I.OBJECT_ID AND G.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'G'  AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI   WHERE  TI.RECORD_STATUS = 'A'   AND TI.OBJECT_TYPE = 'G'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID  )  ");
              if (enableRole){
                sqlStat.append("      UNION ");
                sqlStat.append("                SELECT R.ROLE_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE   FROM   INVITEE_PICKER_HISTORY I      JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID AND R.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'R' AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (   SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI  WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'R'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID )  ");
              }
            } else if (enableRole) {
              sqlStat.append("      UNION ");
              sqlStat.append("                SELECT R.ROLE_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE   FROM   INVITEE_PICKER_HISTORY I      JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID AND R.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'R' AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (   SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI  WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'R'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID )  ");
            }
          } else if (enableGroup) {
            sqlStat.append("                SELECT G.GROUP_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE  FROM   INVITEE_PICKER_HISTORY I     JOIN USER_GROUP G                             ON G.ID = I.OBJECT_ID AND G.RECORD_STATUS = 'A' WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'G'  AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = ( SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI   WHERE  TI.RECORD_STATUS = 'A'   AND TI.OBJECT_TYPE = 'G'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID  )  ");
            if (enableRole){
              sqlStat.append("      UNION ");
              sqlStat.append("                SELECT R.ROLE_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE   FROM   INVITEE_PICKER_HISTORY I      JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID AND R.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'R' AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (   SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI  WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'R'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID )  ");
            }
          } else if (enableRole) {
            sqlStat.append("                SELECT R.ROLE_NAME FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE   FROM   INVITEE_PICKER_HISTORY I      JOIN USER_ROLE R                            ON R.ID = I.OBJECT_ID AND R.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'  AND I.OBJECT_TYPE = 'R' AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (   SELECT MAX(TI.ACT_DATE)   FROM   INVITEE_PICKER_HISTORY TI  WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'R'  AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID )  ");
          } else {
            sqlStat.append("                SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE    FROM   INVITEE_PICKER_HISTORY I      JOIN USER_RECORD U  ON U.ID = I.OBJECT_ID AND U.RECORD_STATUS = 'A'  LEFT   JOIN STAFF_RECORD S  ON S.ID = U.USER_LINK_ID  AND S.RECORD_STATUS = 'A'  WHERE  I.RECORD_STATUS = 'A'   AND I.OBJECT_TYPE = 'U'  AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND I.ACT_DATE = (  SELECT MAX(TI.ACT_DATE)     FROM   INVITEE_PICKER_HISTORY TI   WHERE  TI.RECORD_STATUS = 'A'  AND TI.OBJECT_TYPE = 'U' AND TI.ACTOR_ID = " + sessionContainer.getUserRecordID() + "  AND TI.OBJECT_ID = I.OBJECT_ID ) ");
          }
          
          sqlStat.append("       ) L ");
          sqlStat.append(" WHERE (");
          sqlStat.append(" (L.RECORD_TYPE = 'U' ");
          for (int n = 0; n < expectUserIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectUserIDs.get(n) + " ");
          }
          sqlStat.append(" ) OR (");
          sqlStat.append(" L.RECORD_TYPE = 'G' ");
          for (int n = 0; n < expectGroupIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectGroupIDs.get(n) + " ");
          }
          sqlStat.append(" ) OR (");
          sqlStat.append(" L.RECORD_TYPE = 'R' ");
          for (int n = 0; n < expectRoleIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectRoleIDs.get(n) + " ");
          }
          sqlStat.append(" )) ");
          sqlStat.append(") R  ");
          sqlStat.append(" ORDER BY R.ACT_DATE DESC, R.FULL_NAME  ");
          
          
          preStat = dbConn.prepareStatement(sqlStat.toString());
          rs = preStat.executeQuery();
          while(rs.next() && i<choices.intValue()){
            column_Id_History.add(rs.getString(this.ID));
            column_Record_Type_History.add(rs.getString(this.RECORD_TYPE));
            column_Full_Name_History.add(rs.getString(this.FULL_NAME));
            column_Email_History.add(rs.getString(this.E_MAIL));
            i++;
          }
          result.add(column_Id_History);
          result.add(column_Record_Type_History);
          result.add(column_Full_Name_History);
          result.add(column_Email_History);
        } else {//search user module DB
          //sqlStat.append("SELECT  L.ID, RECORD_TYPE, FULL_NAME, E_MAIL FROM    ( SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL FROM   USER_RECORD U LEFT   JOIN STAFF_RECORD S ON S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' WHERE  U.ID <> 0 UNION SELECT G.GROUP_NAME, G.ID, 'G', NULL FROM   USER_GROUP G WHERE  G.RECORD_STATUS = 'A' UNION SELECT R.ROLE_NAME, R.ID, 'R', NULL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ) L WHERE L.FULL_NAME LIKE '" + key + "%' OR L.FULL_NAME LIKE '% " + key + "%' ORDER BY L.FULL_NAME");
          sqlStat.append(" SELECT  L.ID, RECORD_TYPE, FULL_NAME, E_MAIL FROM    (");
          if (enableUser){
            sqlStat.append(" SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL FROM   USER_RECORD U LEFT   JOIN STAFF_RECORD S ON S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' WHERE  U.ID <> 0 AND U.RECORD_STATUS = 'A' ");
            if (enableGroup){
              sqlStat.append(" UNION ");
              sqlStat.append(" SELECT G.GROUP_NAME FULL_NAME, G.ID, 'G' RECORD_TYPE, NULL E_MAIL FROM   USER_GROUP G WHERE  G.RECORD_STATUS = 'A' ");
              if (enableRole){
                sqlStat.append(" UNION ");
                sqlStat.append(" SELECT R.ROLE_NAME FULL_NAME, R.ID, 'R' RECORD_TYPE, NULL E_MAIL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ");
              } else {
                //do nothing
              }
            } else if (enableRole){
              sqlStat.append(" UNION ");
              sqlStat.append(" SELECT R.ROLE_NAME FULL_NAME, R.ID, 'R' RECORD_TYPE, NULL E_MAIL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ");
            } else {
              //do nothing
            }
          } else if (enableGroup){
            sqlStat.append(" SELECT G.GROUP_NAME FULL_NAME, G.ID, 'G' RECORD_TYPE, NULL E_MAIL FROM   USER_GROUP G WHERE  G.RECORD_STATUS = 'A' ");
            if (enableRole){
              sqlStat.append(" UNION ");
              sqlStat.append(" SELECT R.ROLE_NAME FULL_NAME, R.ID, 'R' RECORD_TYPE, NULL E_MAIL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ");
            } else {
              //do nothing
            }
          } else if (enableRole){
            sqlStat.append(" SELECT R.ROLE_NAME FULL_NAME, R.ID, 'R' RECORD_TYPE, NULL E_MAIL FROM   USER_ROLE R WHERE  R.RECORD_STATUS = 'A' ");
          } else {
            //do nothing
            sqlStat.append(" SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL FROM   USER_RECORD U LEFT   JOIN STAFF_RECORD S ON S.ID = U.USER_LINK_ID AND S.RECORD_STATUS = 'A' WHERE  U.ID <> 0 AND U.RECORD_STATUS = 'A' ");
          }
          sqlStat.append(" ) L ");
          sqlStat.append(" WHERE (L.FULL_NAME LIKE '" + key + "%' OR L.FULL_NAME LIKE '% " + key + "%') AND ");
          sqlStat.append(" ( ");
          sqlStat.append(" (L.RECORD_TYPE = 'U' ");
          for (int n = 0; n < expectUserIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectUserIDs.get(n) + " ");
          }
          sqlStat.append(" ) OR (");
          sqlStat.append(" L.RECORD_TYPE = 'G' ");
          for (int n = 0; n < expectGroupIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectGroupIDs.get(n) + " ");
          }
          sqlStat.append(" ) OR (");
          sqlStat.append(" L.RECORD_TYPE = 'R' ");
          for (int n = 0; n < expectRoleIDs.size(); n++){
            sqlStat.append(" AND L.ID <> " + expectRoleIDs.get(n) + " ");
          }
          sqlStat.append(" )");
          sqlStat.append(" ) ");
          sqlStat.append(" ORDER BY L.FULL_NAME ");
          
          //sqlStat.append("  SELECT DISTINCT W.ID,W.RECORD_TYPE,W.FULL_NAME,W.E_MAIL FROM   (         SELECT  H.ID, H.RECORD_TYPE, H.FULL_NAME, H.E_MAIL         FROM   (                     SELECT L.FULL_NAME, L.ID, L.RECORD_TYPE, L.E_MAIL, L.ACT_DATE                 FROM   (                         SELECT U.FULL_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, S.EMAIL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_RECORD U                                ON U.ID = I.OBJECT_ID                         LEFT   JOIN STAFF_RECORD S                                 ON S.ID = U.USER_LINK_ID                                 AND S.RECORD_STATUS = 'A'                          WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'U'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                         UNION                         SELECT G.GROUP_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_GROUP G                                ON G.ID = I.OBJECT_ID                         WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'G'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + "                         UNION                         SELECT R.ROLE_NAME, I.OBJECT_ID ID, I.OBJECT_TYPE RECORD_TYPE, NULL E_MAIL, I.ACT_DATE                         FROM   INVITEE_HISTORY I                         LEFT   JOIN USER_ROLE R                                ON R.ID = I.OBJECT_ID                         WHERE  I.RECORD_STATUS = 'A'                                AND I.OBJECT_TYPE = 'R'                                AND I.ACTOR_ID = " + sessionContainer.getUserRecordID() + " ) L                 ORDER BY L.ACT_DATE DESC, L.FULL_NAME ) H         UNION         SELECT R.ID, R.RECORD_TYPE, R.FULL_NAME, R.E_MAIL         FROM   (                  SELECT  L.ID, L.RECORD_TYPE, L.FULL_NAME, L.E_MAIL                   FROM    (                              SELECT U.FULL_NAME, U.ID ID, 'U' RECORD_TYPE, S.EMAIL E_MAIL                              FROM   USER_RECORD U                              LEFT   JOIN STAFF_RECORD S                                     ON S.ID = U.USER_LINK_ID                                     AND S.RECORD_STATUS = 'A'                              WHERE  U.ID <> 0                              UNION                              SELECT G.GROUP_NAME, G.ID, 'G', NULL                              FROM   USER_GROUP G                              WHERE  G.RECORD_STATUS = 'A'                              UNION                              SELECT R.ROLE_NAME, R.ID, 'R', NULL                              FROM   USER_ROLE R                              WHERE  R.RECORD_STATUS = 'A' ) L                  ORDER BY L.FULL_NAME                ) R         ) W ");
          preStat = dbConn.prepareStatement(sqlStat.toString());
          rs = preStat.executeQuery();
          while(rs.next() && i<choices.intValue()){
            column_Id.add(rs.getString(this.ID));
            column_Record_Type.add(rs.getString(this.RECORD_TYPE));
            column_Full_Name.add(rs.getString(this.FULL_NAME));
            column_Email.add(rs.getString(this.E_MAIL));
            i++;
          }
          result.add(column_Id);
          result.add(column_Record_Type);
          result.add(column_Full_Name);
          result.add(column_Email);
        }
        return result;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
    } catch (Exception e) {
      log.error(e, e);
    } finally {
      try {
        rs.close();
      } catch (Exception ignore) {} finally {
        rs = null;
      }
      try {
        preStat.close();
      } catch (Exception ignore) {} finally {
        preStat = null;
      }
    }
    return null;
  }
  
 /**
  * get the user group id by user id
  * @param userID
  * @return userGroupID
  * @throws ApplicationException
  */
  public synchronized Integer getUserGroupIDbyUserID(Integer userID) throws ApplicationException {
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    PreparedStatement preStat = null;
    Integer userGroupID = null;
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.USER_GROUP_ID FROM MTM_USER_RECORD_USER_GROUP A WHERE A.USER_RECORD_ID = ? AND RECORD_STATUS ='A' ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, userID);
        rs = preStat.executeQuery();
        if (rs.next()) {
          userGroupID =getResultSetInteger(rs, "USER_GROUP_ID");
        }
        return userGroupID;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(preStat);
      }
    }
  }
  
  /**
   * get the user role id by user id
   * @param userID
   * @return userRoleID
   * @throws ApplicationException
   */
  public synchronized Integer getUserRoleIDbyUserID(Integer userID) throws ApplicationException {
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    PreparedStatement preStat = null;
    Integer userRoleID = null;
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.USER_ROLE_ID FROM MTM_USER_RECORD_USER_ROLE A WHERE A.USER_RECORD_ID = ? AND RECORD_STATUS ='A' ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, userID);
        rs = preStat.executeQuery();
        if (rs.next()) {
          userRoleID =getResultSetInteger(rs, "USER_ROLE_ID");
        }
        return userRoleID;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(preStat);
      }
    }
  }
}
